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
-
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'
...
ENDIt 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"0
Answers
-
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'
...
ENDIt 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"0 -
@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"0 -
Thank you - you are correct. I updated to 0 to include those resolved in the same day.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 688 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive