3 Replies Latest reply on Apr 3, 2018 11:59 PM by Rajhesh Kumar A S A

    Time taken to resolve tickets (Stacked bar view)

    Rajhesh Kumar A S A

      Hi Experts,

       

                  I need to calculate the tickets queue per teamwise. As, you are aware, Tickets are raised for certain issues and these tickets travel team to team (like 1st level, 2nd Level, technical, etc.) Now all I need to find is 2 things. 1) Time taken to address the ticket. (i.e. to start working on the ticket)  2) Time taken by each team to investigate on the ticket

      Above two things needs to be calculated dynamically (as the data differs each day). below is the sample data.

       

       

      From the above, if you see...the time taken to address needs to be calculated only once for a ticket(i.e. Assign Time - ticket creation Time) in hrs, for the first appearance only.

      Time taken to investigate will be calculated as per teamwise till closure of the date. So, if you take the TT1819656, first team to investigate on the ticket is ABC and the time taken is calculated from (3rd Row, 3rd Column - 2nd row, 3rd column) (in hrs), until the handover of ticket happens from ABC to DEF. It goes on like this, till the last team (ABC) who works on the same ticket and for the last team on the same ticket, the time should be calculated from last assign time to  last closed date. (i.e. 5th row 4th column - 5th row 3rd column).

       

      Final output will be needed as below, in a stacked bar view

       

       

       

      Hope you can understand by requirement. Moreover, the tickets will not be in order. so the ticket sorting needs to happen initailly (according to Assign date (assending time) for the same ticket, and then the calculation should happen. Kindly try your best to give me the solution. Thanks.

       

      From the above output, we are able to track, how long the ticket was in the queue to address and how long each team has worked on the same ticket.

       

      Attached the sample input and output in excel sheet, for reference.