2 Replies Latest reply on Jun 22, 2018 5:48 AM by Alex Braun

    Need help writing a calculation

    Anjali Sn

      I need help in writing a calculation, I have the following values: resolution time for each ticket (say r), number of days (d) i.e., 30 days, 60 days etc., I want to calculate:

       

      (total resolution time of all tickets received in day1/total tickets received in day1 + total resolution time of all tickets received in day2/total tickets received in day2 + ...) / number of days

       

      I'm trying the following:

       

      SUM(CASE [Status]

      WHEN "Resolved" THEN

      ('day',[Created Time],[Resolved Time])

      END)

      /

      COUNT(CASE [Status]

      WHEN "Resolved" THEN [ID]

      END)

       

      But the above will give me : sum of total resolution time of all the tickets in selected number of days/ total tickets received in the selected number of days. Not what I want.

       

      Please help!

        • 1. Re: Need help writing a calculation
          Shinichiro Murakami

          Hi Anjali

           

          Could you please attach your sample data as twbx format.

           

          Thanks,

          Shin

          • 2. Re: Need help writing a calculation
            Alex Braun

            You can use an LOD calc for this.

             

            sum({fixed [day]: sum([Resolution time])}/{fixed [day]: count([Tickets])})

             

            {fixed [day]: sum([Resolution time])} -----This will give you the sum of resolution time by day

            {fixed [day]: count([Tickets])} ---------This will give you the count of tickets by day

             

            divide the first by the second to get avg resolution based on ticket entry date

             

            then sum everything together

             

            You may want to put resolved on a filter, or  you can create other calculated fields to populate resolution time and tickets as you need.