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 Giving agents access 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.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query.
- In query builder, click the calculations icon (
) on the right sidebar and then 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 or paste 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.
15 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.
I followed these instructions to a tee, and the report doesn't resemble what is shown.
Hello Jim Collins,
So I think you may have the wrong type of graph as well as having not enough dates selected from the image you shared with us. I would recommend still starting with a row chart just in the initial setup and ensuring you have the "Ticket Created - Date" selected. Go ahead and run a check on what I mentioned and let me know if you are still having this issue.
Best regards.
Hi,
Kristina Fronheiser has setup a dashboard called Zendesk Ticket reporting and has posted a picture of it 4 months ago. I am new to Zendesk and would love to get something setup like this for my teams. How would I go about this?
Kind Regards
Hi Robert Magee
Happy to try and help here. This dashboard looks like it is leveraging several queries and some custom dashboard formatting. The linked articles should set you on the proper course... And speaking of courses, I would highly recommend checking out some of the training resources available via the learning portal.
Brandon
Please sign in to leave a comment.