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

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.

 

Best Answer

  • AS
    AS Coach
    Answer ✓

    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.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach
    Answer ✓

    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.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Chips
    Chips Domo Employee

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

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Thank you - you are correct. I updated to 0 to include those resolved in the same day.

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