SLA/Response Time Beast Mode

Shumilex

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!

GrantSmith

Hi @Shumilex

Have you tried starting with a table card and making sure your data is being populated correctly? Are you getting values 1-24+ for your series? What are you using for your value?

Shumilex

Hi @GrantSmith

Response Time.xlsx

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.

GrantSmith

Hi @Shumilex

When dealing with datetimes I prefer to utilize the UNIX_TIMESTAMP function to get the number of seconds since 1970-01-01 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 1970-01-01 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.

jaeW_at_Onyx

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.

Shumilex

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.

@jaeW_at_Onyx @GrantSmith

GrantSmith

@Shumilex

How is it not working correctly? What values are you getting for hours <1, 1, 2, 3? Which calculation are you utilizing?