Explore recipe: Top problem tickets by unsolved incidents

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
• Editor or Admin permissions (see Giving agents access to Explore)
• Ticket data in Zendesk Support

Creating the query

To create the query

1. In Zendesk Explore, click the query () icon.
2. In the Queries Library, click New Query.
3. Now, choose a dataset containing the problem ticket data you'll need to build the query.
   
   Select Zendesk: Tickets. Query Builder opens.
   
   
   
   
4. In the Metrics panel, click Add.
5. In the metric list, expand Tickets, click Incidents, and then click Apply.
6. In the Rows panel, click Add.
7. In the rows list expand the Ticket menu, then click Ticket problem ID.
8. Now, add a filter to exclude incidents that are not connected to problem tickets. Click Ticket problem ID and click the Excluded tab. Select Null to exclude any null values.
9. 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.
10. 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

    It will look like the screenshot below:
11. From the Metrics panel, add the new metric Calculated metrics. Ensure the metric aggregator is set to COUNT. For more information, see Choosing metric aggregators.
    
12. Now, add the percentage detail using a Result metric calculation. Click Result manipulation (), then select Result metric calculation. 
13. On the Result metric calculation page, click Add a new metric.
14. Enter the name of your metric.
15. Enter the formula below. You must select your metrics and functions from the drop-down list next to Insert.
    

    COUNT(Unsolved incidents)/COUNT(Incidents)

    
16. Click + Add to add the calculation.
17. 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 %.
    
    Then the query is done! Check out the screenshot below as an example.
    

If you'd like 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.