In this Explore recipe you'll learn to create a report that shows last month's ticket count compared to a 12 month average.
What you'll need
Skill level: Intermediate
Time Required: 5-10 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Adding users to Explore)
- Ticket data in Zendesk Support
How to create the report in Explore
Use the following steps to create this 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. Query builder opens.
- Next, you'll create a standard calculated metric to find the monthly average for all time. You can use this formula to do so:
COUNT(Tickets)/DCOUNT_VALUES([Ticket created - Month])Be sure to check compute separately.
- Next, using the above custom metric, create a date range calculated metric to only return monthly averages for the last 12 months. In a new Date range calculated metric, under Original metric select your newly created custom metric, and for Defined on select Ticket created.
- Under Date range, select the Advanced tab and select:
- From the beginning of: 12 months in the past
- To the end of: 1 month in the past
- When you are finished, click Save.
- Now that you've created the necessary metrics, you can build your query. In the Metrics panel of the query, add the default metric COUNT(Tickets Created - Last Month) and your custom date range calculated metric. Be sure to keep Tickets Created - Last Month listed as the first metric.
- Under Visualization type select the KPI visualization. This will show the variation between tickets created last month and your monthly average.
- After changing to a KPI chart, go to Chart configuration > Chart and select Show variation. Choose to display this variance as a percentage or difference.