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 reports () icon.
- In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Tickets, then click Start report. The report 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 report. In the Metrics panel of the report, 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.
This worked great for me, is it possible to get a weekly average though so I can compare last week to the weekly average?
What is the "end date" supposed to be for the "12 month average" metric? That step isn't listed, and "All time" makes the report break, but if I set it to "ended 1 month ago" it's not actually a 12 month rolling average.
Hi CJ Johnson, excellent question! I tested this out, and it looks like "To the end of: 1 month ago" gives the right number for a 12-month average.* I updated the recipe with that as the end date for the date range calculated metric.
*A little more explanation on the 12-month average: "From the beginning of: 12 months in the past" looks back as far as the beginning of the current month in the previous year—meaning, if I run the report today, it looks back as far as May 1, 2021. "To the end of: 1 month in the past" stops at the last day of the previous month (so April 30, 2022 for my example report from today). So that should give you 12 full months of data to calculate the average.
Please sign in to leave a comment.