5 Replies Latest reply: Jul 31, 2018 9:42 AM by Lauri Scharf RSS

    Complex Aging and Date Calculations

    Aaron Falk-Mahapatra

      Hello,

       

      I have been trying to determine what is the best avenue to take to create an app that analyzes the age and condition of customer issue tickets we have had in our system over a 6 month period.

       

      So I have a data set with tickets that have both an open date and close date (or the close date field is empty if the ticket is still open). The list for the last 6 months includes any open items at the end of the 6 month period, all items that were closed in that six month period regardless of when they were opened, and all tickets that were created in the six month period. Basically all activity during the 6 months.

       

      For example the date would like this table:

        

      Ticket NumberOpen DateClose Date
      1081201/12/20182/18/2018
      1081272/2/20183/30/2018
      1081452/23/2018
      1081783/16/20184/11/2018
      1081814/6/20184/9/2018
      1081854/27/2018
      1082115/18/2018
      1082166/8/20186/21/2018
      1082356/29/2018

      What I have been trying to figure out is how to create an app that dynamically recalculates the average age of open tickets, average days to close closed tickets based on the month end date of a currently selected month. So if the user selects April the app will show the average age of all tickets that were open at 4/30/2018 even if those tickets were closed in May or June.

       

      I have been reading and testing out set analysis in chart functions with exclusions, unions, and intersections but I have not been successful at getting this to work. Is set analysis even the way to go with these types of calculation? Should I look at somehow setting up another table with the aging information at each month end?

       

      Is there a guide to setting up these more complicated date and age calculations somewhere?

       

      Thank you in advance for any guidance you can give me!