Admins commonly use custom fields to collect information that Zendesk Support doesn't collect by default. For example, you might want to add the office location to a ticket or the product type a ticket is about. Explore collects custom field information to enable you to add it to your reports.
In this Explore recipe, you'll learn a useful method to compare the number of tickets created between two time periods. Then, you'll break this number down by the values in a custom field.
This recipe contains the following sections:
What you'll need
Skill level: Easy
Time Required: 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 Zendesk 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 two date range calculated metrics. The first will display the tickets created this month and the second will display tickets created last month. In the Calculations menu (
), click Date range calculated metric.
-
On the Date range calculated metric page, configure the following values:
- Name: Tickets created last month
- Original metric: Tickets
- Defined on: Ticket created
- Date range: Simple > Last month
When you're finished, click Save.
-
Using the same steps, create a second date range calculated metric with the following settings:
- Name: Tickets created this month
- Original metric: Tickets
- Defined on: Ticket created
- Date range: Simple > This month
When you're finished, click Save.
- In the Metrics panel of the report builder, click Add.
-
From the list of metrics, expand Calculated metrics and choose the two metrics you just created, Tickets created this month and tickets created last month. Make sure to change the aggregator for both metrics to COUNT. Your report will look similar to this example:Tip: You can change the two date range calculated metrics to cover any time period you want, for example, this week and last week or this year and last year.
- Next, create a result metric calculation. This will show the percentage difference between last months tickets and this months tickets. For more help with result metric calculations, see Using result metric calculations. From the result manipulation menu (
), click Result metric calculation.
-
On the Result metric calculation page, click Add a new metric, then configure the following values:
- Name: % Change month
- Formula: (COUNT(Tickets created this month)-COUNT(Tickets created last month))/(COUNT(Tickets created this month))
- Disable the Clear used metrics check box
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
- When you're finished, click Add. Your calculation will look like this example:
- Now, you need to change the display format of the result metric calculation to display as a percentage. In the report builder, click the chart configuration (
) menu, then click Display format.
- On the Display format page, expand the SUM(% Change month) dropdown, then click %.
- Close the Display format page. Your report will now look similar to this example:
- Finally, we want to break the results down by a custom field. This example uses a custom field named favorite fruit. Custom fields are available as an attribute in Explore. In the Rows panel, click Add.
- From the list of attributes, expand Ticket custom fields, click favorite fruit (or your own custom field attribute), then click Apply. Your report will look similar to the following example:
6 comments
Taylor Bowser
Hi pstrauss,
You can duplicate the default COUNT(Tickets) metric and add it to your report. Then you can go to result manipulation>>result path calculation and select Pattern = % of total on rows for the duplicate metric as shown below:
![](/hc/user_images/Qg2onzoS99jjW8Jf4OE2eg.png)
Hope this helps!
1
PAUL STRAUSS
Ahh, thanks, Taylor. That's helpful!
0
Louise Dissing
Hi!
I followed this recipe and, unfortunately, I found an error!
When there's nothing to count (eg. no tickets created last month), it won't return with '0' or NULL, and the calculations will be wrong or empty.
Try to look at this:
Have anyone encountered this issue and solved it?
Thanks! :-)
1
Rob Stack
Hi Louise, thanks for the question. I've opened a ticket on your behalf so we can investigate what's going on. I'll them make any necessary updates to the article. You should hear from someone soon. Thanks!
-1
BA
The article states that the percent change is calculated by:
It should in fact be calculated by:
(COUNT(Tickets created this month)-COUNT(Tickets created last month)) /COUNT(Tickets created last month).
Based on the formula stated in the article, if I had 1 ticket last month and 1000 tickets this month, it would show a 99.9% increase in tickets. But a 100% increase means a doubling of the number of tickets, not a 1000x increase in the number of tickets.
Additionally, the screenshot in step 14 shows 48,686 tickets this month and 79,883 tickets last month, with a change of 60.95%. It appears the formula used to calculated the % change in that image is just (# tickets this month)/(# tickets last month), which is also incorrect, but a different sort of incorrect compared to Step 10.
The comment by Louise Dissing demonstrates not a problem with Zendesk, but just the mathematical reality of having 0 in the denominator, which is not really a legal operation.
Our method of dealing with that was just to "pretend" that there was actually 1 ticket in the previous period when calculating the percentage increase using the following:
So going from 0 tickets to 3 tickets shows a 300% increase - mathematically, that's completely wrong, but for our use case, it's fine, and it's more helpful than displaying nothing at all.
0
Gabi
Hi, I’m looking for something similar but with every month of the year, displaying the percentage change between each month. Ideally, it would be dynamic so we can adjust the dates as needed. Do you know how we can achieve this?
0