In this Explore recipe, you'll learn how to create a query that shows the top problem tickets by number of unsolved incidents that are attached. If you're not familiar with working with problem type tickets, check out Working with problem and incident tickets.
What you'll need
Skill level: Moderate
Time Required: 20 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 > Tickets > Support: Tickets, then click New query. Query builder opens.
- In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Incidents, then click Apply.
- In the Rows panel, click Add.
- From the list of attributes, choose Ticket > Ticket problem ID, then click Apply.
- Now, add a filter to exclude incidents that are not connected to problem tickets. Click the Ticket problem ID attribute, then click the Excluded tab. Select Null to exclude any null values.
- Now, create a custom Standard calculated metric to calculate the number of incidents that are currently unsolved. Click Calculations (
), and then click Standard calculated metric.
- Copy the formula below and paste it into the metric editor.
IF ([Ticket type - Unsorted] = "Incident" AND (([Ticket status - Unsorted] != "Solved")
AND ([Ticket status - Unsorted] != "Closed")))
THEN [Ticket ID]
ENDIF - Give the metric a name like Unsolved incidents. The metric will look like the screenshot below:
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - From the Metrics panel, add the new metric Calculated metrics > Unsolved incidents. Ensure the metric aggregator is set to COUNT. For more information, see Choosing metric aggregators.
- Now, add the percentage detail using a Result metric calculation. Click Result manipulation (
), then select Result metric calculation.
- On the Result metric calculation page, click Add a new metric.
- Enter the name of your metric.
- Enter the formula below. You must select your metrics and functions from the drop-down list next to Insert.
COUNT(Unsolved incidents)/COUNT(Incidents)
- Click + Add to add the calculation.
- The final step is to edit the query to show the new calculation in a percentage format. Click Chart configuration (
), then Display format. Then change the calculation metric to display in %.
The query is now complete. See the screenshot below for an example.
If you want to filter this report by the problem tickets with the most incidents, you can apply a top/bottom filter. Check out Creating a top/bottom filter for further instructions.
11 Comments
This is exactly the recipe we were needing!
One thing I'm trying to figure out is how to display the Problem Ticket Subject. While a Problem ID is nice, it doesn't exactly explain what the actual Problem is. :)
Adding "Ticket Subject" to the Rows only displays the Subject of the Incident tickets.
Any suggestions or ideas from anyone on how to make this possible?
Hiya Travis!
Glad you like the recipe so far :) I did some testing on this and talked to our team. Unfortunately we weren't able to come up with a good solution for this as any attributes added will automatically reference the metrics, in this case the Incident tickets.
With that said, our team is going to put this in as a feature request. I can't make any promises, but hopefully we can find a solution for you. We'll post any progress here.
Keep the questions coming!
+1 Travis,
It would be fantastic to see the subject field.
The "% Unsolved incidents" is not working, not sure why.
I'm using "Product affected" (basically is a list of product we use) instead of "Ticket problem ID" but his shouldn't metter
Hey Francesco,
I'm going to bring this into a ticket so our Customer Advocacy team can dig into this further. They'll most likely need to take a look at the specific report in question to determine the cause of this issue.
You'll receive an email shortly stating your ticket has been created.
Cheers!
This is great! I would like to show the subject line of the problem in my report but when i pull in Ticket Subject it brings the subject lines from the incidents. Is there a way to bring the problem subject line in?
Hey Kelly,
This can't be accomplished directly, however, from an incident ticket you can pull the [ticket problem id] and could potentially make a clickable link from that using this approach Explore Recipe: Configuring clickable links to tickets
Hope this helps!
Hi,
I am trying to create the report as explained, however I have different result which I could not find what caused it. In the ticket it shows correctly how many incidents ticket linked as picture below:
However when I created the report in explore:
It only shows 45 instead of the full number which supposed to be 124. The problem ticket is already in solved state - thus all the tickets are also solved (some is closed). I thought first that the ticket status (solved or closed) is the cause, however it is not. Can somebody help where I should check or what is wrong?
Cheers!
Hello Sylvia Agung,
This recipe shows problem tickets by number of unsolved incidents that are attached. Since incidents may be re-opened after a problem ticket is closed, this is likely the case here and the 45 incidents you are seeing are all re-opened tickets.
Hi,
I come across another issue when my unsolved incidents does not retrieve any result.
Below we can see I have unsolved tickets with different statuses than solved.
However after following the steps as mentioned in this article, the column of "unsolved incident" still shows 0.
Can anyone help here?
Thankyou!
Hey Sylvia,
I'm going to create a ticket on your behalf as we will need to take a look at some tickets in your account to determine why these numbers do not match up in your reporting.
You'll receive an email shortly stating your ticket has been created.
Cheers!
Please sign in to leave a comment.