Tip: Year over Year Reporting Using "Running Year (Month)" Custom Attribute
FeaturedI wanted to share some steps I came up with for reporting ticket volume in a way that allows you to see % change year-over-year by month. This requires creating custom metrics and attributes in Zendesk Explore. Hope this is useful to some folks out there in Explore world!
1. Create a new custom metric called "Tickets Created - Current 12 Months (YoY)": Returns the total number of tickets submitted within the latest 12 month period. Here is the formula/code:
IF (DATE_GREATER_OR_EQUAL([Ticket created - Date],
DATE_ADD(START_OF_MONTH(NOW()),"year",-1))
AND DATE_LESS([Ticket created - Date],
START_OF_MONTH(NOW())))
THEN [Ticket ID]
ENDIF
2. Create a custom metric called "Tickets Created - Previous 12 Months (YoY)": Returns the total number of tickets submitted within the previous 12 month period. Here is the formula/code:
IF (DATE_GREATER_OR_EQUAL([Ticket created - Date],
DATE_ADD(START_OF_MONTH(NOW()),"year",-2)))
AND DATE_LESS([Ticket created - Date],
DATE_ADD(START_OF_MONTH(NOW()),"year",-1)))
THEN [Ticket ID]
ENDIF
3. Create a custom attribute called "Running Year (Month)": This allows you to layer "Ticket created - Month" on top to see the comparison while maintaining the correct order for the months. Make sure you use the "Sort like a time attribute" checkbox when setting this up. Here is the formula:
IF(MONTH_NUMERIC([Ticket created - Date])<MONTH_NUMERIC(NOW())
AND YEAR([Ticket created - Date])>YEAR(DATE_ADD(NOW(),"year",-2)))
THEN NUMBER(CURRENT_YEAR())
ELSE NUMBER(CURRENT_YEAR()) - 1
ENDIF
4. On your query, Add a Date Range filter to grab tickets created in the last 24 months.
5. Add your metrics to the query, first the "Current Year", then the "Previous Year".
6. Add your custom Running Year (Month) and Ticket created - Month attributes as rows. This should give you a nice table:
7. Use the "Result Metric Calculation" option to create a new YoY comparison metric. The formula there would be (Current Year - Previous Year) / Previous Year. Be sure to check the "Clear used metrics" option:
8. When the new metric loads, change the display format of the metric to %
The result is this:
9. So now you have the core data to build this. To get it looking nice, toggle the columns/rows and change the chart type to Line
9. Duplicate your Tickets Created - Current 12 Months (YoY) metric. You can name this Tickets Created - Current 12 Months
10. Add the duplicated metric you just created as a dual-axis metric in the query builder. This should automatically set the dual metric as a column.
12. Do some clean-up/cosmetic work. I usually remove the "show applied filters" option, and I like to use the "multi-level" option on the x-axis, put the legend at the top, and display the values, etc.
Here's what my finished one looks like on my dashboard:
Good luck!
-
Your second metric syntax comes up with an error now for some reason. Love this idea and would appreciate the help.
-
Hey there - thanks for checking this out. I'm looking at the version on my own dashboard, and I see I'm using the `TODAY()` function instead of `NOW()`. Let me know if that fixes the issue - if not, it'd be helpful to know what error you're sseing.
Thanks!
Please sign in to leave a comment.
2 Comments