3 Replies Latest reply on Sep 19, 2016 2:02 PM by Ivan Young

    Challenge with dates and calculated field

    Robert Pinder

      Hello all,

       

      I work with a data set of tickets and I am stumped.  The background:

      • Each ticket has a "date created" and a "date closed" field. 
        • The date created field is populated when the ticket is created. 
        • The date closed field is not populated until the ticket is closed.
        • Each of the tickets can be open for several months.

      I need to report the number and average days open for the tickets at the end of each month, for the previous 12 months.  My approach thus far is to create a calculated field that determines the days open for a ticket.  But I end up creating a separate calculated field for each month. As an example,  a ticket was opened 15 January and was closed on 15 March.  This results in the following:

       

      • January days open: if date closed is null or > 31 January then days open = end of month date (31 January) - date created (15 January)= 16 days open
      • February days open: if date closed is null or > 28 February then days open = end of month date (28 February) - date created (15 January) = 44 days open
      • March days open: if date closed is null or > 31 March then days open = end of month date (31 March) - date created = null (null value because ticket was closed on 15 March)
      • THis ticket would be counted as open in January and February but not in March.

       

      Next, I build a graph to show the total number of tickets at the end of each month and a second graph with the average days open for open tickets by month.  This is where I am stumped.  in the example above, I have three separate calculated fields.  However, I need to plot the three days open values (January: 16, February: 44, March: null) in a "days open" graph and a "number of tickets open" graph.

       

      Any ideas or suggestions?

       

      thanks!

       

       

      the tickets