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 or Enterprise
- 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 ">3 days"
ELSE "Solved"
ENDIF
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - 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.
30 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 Flagship Credit Acceptance, LLC,
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
Hi there, I am trying to accomplish something similar by reporting out on all tickets with the following conditions
The recipes are great but not exactly what I am looking for.
I'm trying to build the formula like so:
if (Value (Unsolved Tickets age (HOURS([Ticket created - Timestamp]) >32)
AND [Ticket priority] = "normal"
THEN ">32 Hours"
ELSE "solved"
ENDIF
And keep getting errors. I'm probably doing this wrong, any assistance would be greatly appreciated.
Thanks
Hi Jed Hollander -
Can you confirm you are using the Support>Tickets>Support: Tickets dataset when creating this metric?
Brandon
Hi Brandon Tidd.
Yes I am.
Hi Jed Hollander
Would you be able to post a screen shot of the error that your'e getting?
Brandon
Here you go. And maybe I'm doing it wrong, but essentially I need to track all tickets in open and pending state from the time that they are created until a set time and if possible add some type of countdown.
Thanks for looking and the assistance.
Hey Jed Hollander,
I think perhaps you might be able to use IF (VALUE(Unsolved tickets age (hrs))>32) in the first line.
Let me know if that helps you.
Brandon
Thanks for the suggestion Brandon Tidd, but would that count the hours from ticket created time stamp?
This should be one in the same (?)
Brandon Tidd thanks for your help here.
Just to confirm I'm doing this right, in theory this formula here:
IF ([Ticket status - Unsorted] != "Solved"
AND [Ticket status - Unsorted] != "Closed"
AND [Ticket priority] = "Normal")
AND (VALUE(Unsolved tickets age (hrs))> "94")
THEN [Ticket ID]
ENDIF
Should return any ticket that is not solved / not closed, ticket priority = normal, and is in an unsolved state but older than 94 hours from the created date timestamp, correct?
Thanks for the help once again.
Update:
I got the above working, had to fix my formula just a tad:
IF ([Ticket status - Unsorted] != "On-Hold"
AND [Ticket status - Unsorted] != "Solved"
AND [Ticket status - Unsorted] != "Closed"
AND [Ticket priority] = "Normal")
AND (VALUE(Unsolved tickets age (hrs))>94)
THEN [Ticket ID]
ENDIF
Returns the following:
Ultimately, what I am trying to do, is to replace that ticket ID in the what-if to display the actual date that the SLA would be considered breach, in this case, 96 hours. is there any way to do this?
Update #2. I think I finally got it after a lot of trial and error.
Formula:
IF ([Ticket priority - Unsorted] ="Normal"
AND [Ticket status - Unsorted] ="New"
OR [Ticket status - Unsorted] ="Open"
OR [Ticket status - Unsorted] ="Pending")
THEN DATE_DIFF([Ticket created - Timestamp],TODAY(), "nb_of_hours")
ENDIF
Result:
Would love to know if someone could check my logic and tell me if I can use the round up in that formula to make these even numbers?
Hey Jed,
You'd want to add a set of () in your formula as shown below:
One of our Explore experts used the above and that seems to work for them.
You can then remove decimals by going to chart configuration>>display format and editing the custom metric's display.
Hope this helps!
Thanks Brett Bowser I updated it and it works properly! Thank you.
About the decimal removal, this is not working, maybe I'm doing it wrong?
Hi there,
I am hoping to get some help here. I am trying to create an explore report that gives the list of tickets that have not been updated by the agent in the last 15 days. I am using Support: Tickets dataset and below is the calculated metric.
IF ([Ticket status - Unsorted] ="Open")
AND ([Ticket type - Unsorted] !="Task")
AND ([Assignee role] = "Agent")
AND (DATE_DIFF (NOW(), [Ticket updated - Timestamp], "nb_of_days") > "15")
THEN [Ticket ID]
ENDIF
This metric pulls the list of tickets that do not have any update in the last 15days. But I am trying to get the list of tickets that specifically do not have an agent update (neither private nor public comment) in the last 15 days.
Best Regards,
MS
Hello Mangai Sivakumar,
I reached out to our in-house experts on this, and right now, there is no native way to accomplish this in Explore. I'm going to share this in our weekly digest and see if anyone has a workaround that could help with building this report.
Best regards.
Please sign in to leave a comment.