How can I calculate SLA for a ticket and how far past SLA the ticket went before resolution?

user09338

Hi all,

 

I have a dataset that includes customer escalation tickets that have been or will be worked and resolved by agents. The data includes CREATE_DATE of the ticket and RESOLUTION_DATE. The tickets have a 5 business day SLA period from their CREATE_DATE to be resolved. What I am trying to calculate is how many days, if any, the tickets went past their 5 day SLA before resolution. Right now I picture the card as a stacked bar graph with the # of days past SLA grouped (i.e - under SLA group, 1-5 days past, 10-20 days past, etc.) and also organized by month.

 

I am not sure if I can do this days past SLA calculation in Beastmode using the Date Difference function, or if I will need to do this some other way? I have experience with simple Beastmode but not much more advanced than that. Apprecaite any input.

 

AS

Does your resolution date get filled in once the ticket is resolved, or is that more of a promise date (create_date + 5)?  That might determine the exact parameters your datediff function receives.

 

In general, your beast mode could look something like this in order to group your tickets.  This assumes your resolution_date is filled when actually resolved, and any non-resolved tickets are null values:

CASE
WHEN DATEDIFF(IFNULL(`RESOLUTION_DATE`,CURRENT_DATE()),`CREATE_DATE`) >= 1 and DATEDIFF(IFNULL(`RESOLUTION_DATE`,CURRENT_DATE()),`CREATE_DATE`)  <= 5 THEN '1-5 Days'
...
END

 

It reads like "If the case isn't resolved, use today's date, otherwise take the resolution date, and find the difference between that and the create date.  If that number is between one and five, that ticket belongs in the 1-5 day group."

Rinse and repeat that basic WITH logic inside the CASE statement for all of your groups.

AS

Does your resolution date get filled in once the ticket is resolved, or is that more of a promise date (create_date + 5)?  That might determine the exact parameters your datediff function receives.

 

In general, your beast mode could look something like this in order to group your tickets.  This assumes your resolution_date is filled when actually resolved, and any non-resolved tickets are null values:

CASE
WHEN DATEDIFF(IFNULL(`RESOLUTION_DATE`,CURRENT_DATE()),`CREATE_DATE`) >= 1 and DATEDIFF(IFNULL(`RESOLUTION_DATE`,CURRENT_DATE()),`CREATE_DATE`)  <= 5 THEN '1-5 Days'
...
END

 

It reads like "If the case isn't resolved, use today's date, otherwise take the resolution date, and find the difference between that and the create date.  If that number is between one and five, that ticket belongs in the 1-5 day group."

Rinse and repeat that basic WITH logic inside the CASE statement for all of your groups.

Chips

@user09338 Can a ticket be resolved on the same day it was created? @AS Might need to slightly amend solution if so, maybe ">=0"?

user09338

Thank you - you are correct. I updated to 0 to include those resolved in the same day.

user09338

The Resolved Date does populate once the ticket has been resolved. The Beast Mode below is what I was looking for - thank you @AS!