SLA/Response Time Beast Mode
Hi Team,
I am currently working on response time for tickets, we eventually want to get to SLA but since the metric will be new for this team we need to understand the average time it takes to handle a ticket.
I am currently using the below to get the average response time displayed as HH:mm:ss
SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(`closedDate`,`createdDate`))))
I want to group by hour so I use the below:
Case
When HOUR(SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(`closedDate`,`createdDate`))))) <= '1' then '1'
ELSE '?'
END)
I do this for all 24 hours and anything beyond 24 hours would be "24+"
The issue lies when I place the sla group as the series, nothing populates. I want a pie chart to display the count and percent of total for each hour so we can identify how long tickets take. I might be thinking too much into this and missing something crucial but thought someone would be able to help.
Thanks in advance!
Answers

Hi @Shumilex
Have you tried starting with a table card and making sure your data is being populated correctly? Are you getting values 124+ for your series? What are you using for your value?
1 
Hi @GrantSmith
Here is an excel with sample data of the closed and created times. I started using the below to get the response time:
Closeddate  Createddate
I cross checked with excel and it was matching, then I was turning the result to a whole decimal number which can be seen in the screenshot below the column "Total Response Time" this would be in days. I turned that decimal number to hours by multiplying by 24. Then I started the case when to create the grouping of "1 hour, 2 hour, 3 hour etc.)
But I looked at some of the results and it was just messed up, it was showing 23 hours but listed hour as 0.000000 but should have been 0.91~
I have given up on this for the next couple of hours.
Thanks for all you do to assist.
0 
Hi @Shumilex
When dealing with datetimes I prefer to utilize the UNIX_TIMESTAMP function to get the number of seconds since 19700101 12:00:00 AM and then doing some math to get the difference in hours.
 Calculates the difference in seconds between both timestamps  UNIX_TIMESTAMP gets the number of seconds elapsed since 19700101 12:00:00 AM  60*60 = 3600 > Number of seconds in an hour.  CASE  More than 24 hours WHEN AVG(UNIX_TIMESTAMP(`closedDate`)  UNIX_TIMESTAMP(`createdDate`)) / (60*60) >= 24 THEN '24+'  Special case with differences less than an hour to put into the 1 hour bucket. WHEN AVG(FLOOR((UNIX_TIMESTAMP(`closedDate`)  UNIX_TIMESTAMP(`createdDate`)) / (60*60))) = 0 THEN 1  Get the number of whole hours (FLOOR) in the time difference ELSE AVG(FLOOR((UNIX_TIMESTAMP(`closedDate`)  UNIX_TIMESTAMP(`createdDate`)) / (60*60))) + 1 END
Hopefully this makes sense. The AVG is taking the average duration in hours.
0 
1) be careful of nesting aggregations, avg, inside the CASE statement. You can't apply the CASE statement. You usually won't get the desired result.
2) you cannot GROUP BY the result of an Aggregation (CASE ... AVG ... )
Case When HOUR(SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(`closedDate`,`createdDate`))))) <= '1' then '1' ELSE '?' END)
I assume the question you want to answer is: "of the tickets that took x number of minutes to close, what
Solution. Calculate Time to Close per row (no aggregation). Then calculate the number of hours that represents. Then put that on the Axis. Then take a simple count OR the Avg for that bucket.
In other words
 TimeDiff_inHours TIME_TO_SEC(TIMEDIFF(`closedDate`,`createdDate`)) / 60 /60
or some variant... this is untested code.
0 
The calculations seem to work when the time is over 24 hours however there are some response time that are <1, 1, 2, 3, 4, 5, 6, 7, etc. hours and that's the issue I'm having because it is not calculating based on the hours, minutes, or seconds.
0 
How is it not working correctly? What values are you getting for hours <1, 1, 2, 3? Which calculation are you utilizing?
0