1 Reply Latest reply on Aug 17, 2015 10:17 PM by Bora Beran

    Help getting a value for every day (date) of the month.

    Jason Cervantes

      My data consists of "tickets" that were opened and closed at different dates/times. I know how to group my data so that i can see how many tickets were opened or closed on specific days of the month. I also know how to calculate how long a ticket was open. What I can't figure out is how to show the total number of tickets that were still open on a given day of the month (as of midnight) as well as the average time open (in days).

       

      An example result set would be: On July 1st I had 140 tickets open and the average open length as of that date was 15 days, on July 2nd I had 135 tickets open with an average open length of 14 days, and so on...  Even if a ticket was never opened on July 2nd I still had x number of tickets still open on July 2nd and I want to see that in my results.

       

      How do i get a column for each calendar day and then use that value in my calculations. I only know how to use field values in calculations.

        • 1. Re: Help getting a value for every day (date) of the month.
          Bora Beran

          It is hard to tell without seeing the data but it looks like to see number of open tickets on a given day, you would need a running total that counts each ticket opened as 1 and closed as -1.

           

          For average open length for each day on your axis, for each ticket you would need to compute days since opened for the tickets that haven't been closed and then do a calculation in a second pass that sums those up and divides by the active ticket count you computed in the first step.