Custom TAT report using lifecycle reports

This article guides you on how to use Life cycle reports and few excel formulae to create a day wise TAT report for tickets created on a given month.

Steps on how to create a life cycle report is given here --> https://support.happyfox.com/kb/article/203-create-life-cycle-reports

Life cycle reports:

Life cycle reports give you an insight on how long a ticket has been in each of the Status/Assignee/Category. In this example, we assume that you would like to see the time each ticket has been on a specific status.

To do this:

  1. Create a lifecycle report by going to Reports >> Lifecycle reports >> Add a new lifecycle report.
  2. Fill the form and choose 'based on' value as 'status' and save it.
  3. From the list of Lifecycle reports, choose the one created and click on  'View'. Select the month and year and download the xlsx report.

Excel:

  1. Open the file in excel. You can see the time in minutes against each ticket, across the statuses you have.
  2. Create a new column in the end that says 'Total Minutes'. Use the formula =SUM(Status1,Status2,..Statusn) where status 1,2,...n will be pending statuses.
  3. Create a new column that says 'Total days'. Here, use the formula ={Total minutes}/60/24.                                      
  4. Create a new column that says 'Completed'. This column should have values 'TRUE/FALSE' denoting whether the ticket is completed. Here, use the formula ={closed status}<>0.
  5. Create a new column that says TAT. This column should have the final values in number of days, only for tickets that has TRUE in the 'Completed' column. Here, use the formula =IF({Completed}=TRUE,ROUNDUP({Total days},0),"NA"). This formula means that if the value under 'Completed' is TRUE, then round up the value under Total days; Else put "NA".
  6. If you just wanted to see the numbers, you can stop here. To have a full summary, go to Data >> Pivot table and click OK.
  7. In the new sheet that is created, Drag and drop 'TAT' in the Row labels and drag drop Ticket ID in values.
  8. The final data ranks the tickets based on their TAT on number of days. Double click on the number of tickets to see the list of tickets from that stack.

Note:

1. The data is pertinent to the time of downloading the report; However you can download fresh report to do this every time.

2. There is an error by minutes in the lifecycle report, leading to few tickets being marked as '0' days. This can be considered as 1 day.

3. If you had previous statuses which you deleted, they may also be listed in the download file, but with '0' minutes marked against them, if the tickets never reached that status.

  • 322
  • 03-Oct-2018
  • 4638 Views