In this Explore recipe, you'll learn how to create a query that shows the week over week percentage change in meeting your SLA targets.
What you'll need
Skill level: Advanced
Time Required: 25 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the query
To create the query
- In Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > SLAs > Support: SLAs, then click New query. Query builder opens.
- Now, create Date range calculated metrics to show the percentage of SLA targets that were met in each date range. From the Calculations (
) menu, click Date range calculated metric.
- Create a new date range calculated metric called SLA % achieved last week and set it up as follows:
- Original metric: % Achieved SLA targets
- Defined on: SLA update
- Date range: Simple - Last week
- Create a new date range calculated metric called SLA % achieved week before last and set it up as follows:
- Original metric: % Achieved SLA targets
- Defined on: SLA update
-
Date range: Advanced
- From the beginning of: 2 weeks in the past
- To the end of: 2 weeks in the past
- In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > SLA % achieved last week and Calculated metrics > SLA % achieved week before last, then click Apply.
- Ensure the metric aggregators for the custom metrics are set to SUM. For more information, see Choosing metric aggregators.
- From the Result manipulation (
) menu, click Result metric calculation.
- In the Result metric calculation menu, click Add a new metric.
- Add the metric below by clicking Insert to add the metrics to the formula.
(SUM(SLA % achieved last week)-SUM(SLA % achieved week before last))/SUM(SLA % achieved week before last)
- When you are finished, click + Add.
- From the Chart configuration (
) menu, click Display format.
- On the Display format page, set all metrics to display in %.
-
From the Visualization type (
) menu, click Table.
The query is complete. See the screenshot below for an example of how it will look.
9 Comments
Is there a way to display the % as a whole number, rather than to one decimal point?
Thank you
Hey, Sarah!
You can change the number of decimal places shown using a Chart configuration setting. Select Chart configuration > Display format, then select Custom next to the metric you want to change this value for. Enter 0 in the Decimal place field:
That should allow you to set the decimal places as needed!
For anyone else who does not have visibility to the SLA datasets (like I was) it is a visibility issue. If you are not seeing the correct datasets you need to go to:
[left pane] Admin (Gear Icon) > Editor Authorization (first icon in triplet in middle of screen)
Then make sure you check all the boxes you need to view the datasets. These permissions should really carry over Insights.
Hi Jessica,
I'm trying to isolate the week over week % change so that it only displays that number as a KPI. Is this possible? Also, without looking at the prior two weeks' numbers, how are you supposed to tell if this week was 12.5% over or under the prior week's SLA percent? Thank you.
Hi Eric, here's a link to where I gave you an answer to this in another article. https://support.zendesk.com/hc/en-us/articles/360022184374/comments/360004007613
Hi Jessica Blake ! im trying to create a new metric based on the existing metric which calculates percentage of achieved SLAs but for only a limited number of policies. I have created this formula but its always showing the percentage as 1. Can you or anyone assist me in what im doing wrong?
IF ([SLA policy name] = "Test1" OR [SLA policy name] = "Test2" OR [SLA policy name] = "Test3" OR [SLA policy name] = "Test4")
THEN
SUM(Achieved SLA tickets) / (SUM(Achieved SLA tickets) + D_COUNT(Breached SLA tickets))
ENDIF
The part after THEN is copied from this metric "SUM(% Achieved SLA tickets)"
Hi Akhter, Shahroze thanks for the question.
The thing that's happening with your formula above is that no matter what the IF statement returns, the same formula is calculated. It doesn't get restricted to the items in the search.
Your best bet might be to use the original SUM formula without the IF statement, i.e.
SUM(Achieved SLA tickets) / (SUM(Achieved SLA tickets) + D_COUNT(Breached SLA tickets))
So, you'll just put this part in the calculated metric.
Then, add the SLA policy name attribute to the Filters panel in your query. Click the filter and choose only the policy names you want.
I hope this help you solve your problem!
This dataset doesn't seem to exist?
Hi CJ Johnson, I just confirmed that this is working for me. Here's an example of me choosing that dataset:
If you're not seeing what I'm seeing here, please let me know and I'll open a ticket on your behalf. Thanks!
Please sign in to leave a comment.