This recipe shows you how to report on the total number of tickets that have not been solved for more than three days.
What you'll need
Skill level: Easy
Time Required: 10 minutes
- Zendesk Explore Professional
- Editor or Admin permissions (see Adding users to Explore)
- Ticket data in Zendesk Support
How to create the report in Explore
- In Explore, click the query (
) icon. - In the Queries library, click New query.
- Under Choose a Zendesk product, select Support.
- Under Choose a dataset type select Tickets and then select the dataset Support: Tickets.
- Click New query.
- In query builder, click the calculations icon (
) on the right sidebar and click Standard calculated attribute. - On the Standard calculated attribute page, enter a Name for the attribute (for example Unsolved tickets > 3 days), and then enter the following formula:
IF (VALUE(Unsolved tickets age (min)) >60*24*3)
THEN "> 3days"
ELSE "Solved"
ENDIF
- Click Save.
- In the Metrics panel, click Add.
- From the list of metrics, choose Unsolved tickets.
- In the Rows panel, click Add.
- From the list of attributes, choose the custom attribute you created Unsolved tickets > 3 days and Ticket created - Date.
The query is displayed as a bar graph.
Excluding blank and 0 results
- In the Result manipulation (
) menu, click Metric filter. - On the Metric filter page, change the slider control to begin at 1 instead of 0, and then click Apply.
11 Comments
This query is no longer working in Explore.
Hello, Kristina,
Which step exactly are you having issues with? I just recreated the query in my test account and on my side it is working fine.
I am getting an error with syntax. Any ideas?
Hey Kristina,
It's possible you're not using the correct dataset which is why you're seeing this error. Can you confirm you're using the Support>Tickets>Support: Tickets dataset when creating this metric?
Let me know!
That worked!
Separate question, is there a way to track tickets that have not been touched in x number of days? I mean any update at all.
I just need to count the number of tickets that meet the criteria of 'not touched in >7 days" per agent.
Hello Kristina Fronheiser,
So there is a "last update date" attribute you can use to accomplish this. You can use this attribute to either filter the query, or use it in a standard calculated metric.
Best regards.
I think I got it, but I'm hoping you can confirm my logic makes sense!
IF ([Ticket status - Unsorted] != "Solved" AND [Ticket status - Unsorted] != "Closed") AND
(DATE_DIFF(NOW(), [Ticket updated - Timestamp], "nb_of_days") > "7")
THEN [Ticket ID]
ENDIF
Hey Kristina Fronheiser,
Everything looks great on our end, excellent job! Let us know if there is anything else you have a question on, but again, great job on crushing this formula, your logic 100% makes sense.
Best regards.
One last question (I hope!)
I've got the following query that's looking at anything that hasn't been touched for more than 14 days. However, it did not return a result. It's blank, no 0 displayed. I tried un-clicking the metric setting to show blank values, but it didn't work. How do I get it to show a 0 when it doesn't return a record?
My query is:
Dashboard:
Hello Kristina Fronheiser,
This article goes into detail on how to include a 0 value in explore reports. Let us know if this helps resolve this error for you, and if there is anything else we can help with, please don't hesitate to ask.
Best regards.
Please sign in to leave a comment.