Comparing metrics to previous periodAnswered
Hi community friends 👋
I created a query to compare tickets created last month to tickets created the previous month. Here's how it looks.
This is great, but I'd ideally like to include something like this on our main dashboard. The problem is, the dates in these custom metrics are essentially hard coded, so if I add this to a dashboard and then filter by date range, it's not going to work. I would ideally like to have a query like this that adjusts with a date filter. So, if I look at the stats for "last week", the comparison would be to the week prior. Whereas if I look at the stats for "last month", the comparison would be to the month prior.
From all my searching and messing around, it doesn't seem like this is possible. Before I write it off completely, I wanted to check if anyone else has been able to achieve this?
Thanks so much,
P.S. Here are some helpful articles I've used when it comes to comparing metrics, in case these are useful for others:
Thanks very much @..., I'm aware of that option. 😊
However, I want the query to adjust based on the date range. I just want the comparison to be based on the date range I've picked, rather than hard coded as last month compared to the previous month. For example:
- If I set "today" in the date picker, I want the comparison to be between today and yesterday
- If I set "last week" in the date picker, I want the comparison to be between last week and the week before
- If I set "this month" in the date picker, I want the comparison to be between this month and last month
Essentially, I want the comparison to be the period I selected and the period immediately preceding it. I feel like this is a pretty common use case and something that is usually available in reporting software. For example, here's how it looks in Google Analytics:
And here in Help Scout:
Date range calculated metrics are fixed to certain dates (not actual dates, but "month" or "week" for example). It seems very cumbersome to have to create multiple queries for every possible comparison you might want (e.g. comparing by day, week, month, quarter, year), so I feel like I'm missing something. 🤔
Thanks @...! Appreciate your help. 🙌
I also worked up an alternative that I'll share here in case it's useful for anyone else. Rather than using date range calculated metrics, I created standard calculated metrics and then configured the result path calculation to show the change between weeks.
Then, on dashboard level, I added a change metric so I can toggle between week, month, and quarter. Video here.
I similarly would really want something like this. Insights had the capability to choose a time window of when tickets were created and it would dynamically compare it to the previous period. This was a huge time saver that makes me really sad to see Insights going away.
Here is what Insights would do. You select the time range and it would show the percentage change from the previous period. In this case, it would compare December 2020 with November 2020.
Hey Bob C
Not sure if you've worked this out now but for anyone else its under Chart Configuration>Chart click Show Variation.
Hannah, you can tell individual components on your dashboard to ignore filters. So when you add this query to a dashboard with a filter, in the query options, choose "Exclude filters" and exclude the dates. https://support.zendesk.com/hc/en-us/articles/360034902733-Best-practices-for-using-dashboard-filters
Apologies - I see I misread your original post.
I don't know of a why to do this date comparison dynamically off the same query. Does sound like a cool feature to possibly share on the feedback channel (I will vote for it!).
In the meantime, the only thing I could think to do is to make multiple metrics for the previous comparison periods. There are metrics for "tickets created past 7 days" and "tickets created past 30 days". They use the "Date range calculated metric" feature - you could make several of your own and have that relative date (week, month, quarter, etc). You could either make these separate queries that populate "boxes" on your dashboard, but all use the date range selected, or you can add all the metrics to the same query. I don't see a way to dynamically choose the period. But you could have several time periods on the dashboard together show them all at once versus changing it based on the user's choice.
Yes, another great alternative. I have done something like this before to show the differences between weeks. I haven't tried toggle between month and quarter. If you get something working well, I encourage you to submit a recipe - I think this is a useful type of query.
Great suggestion, Hillary!
Hannah, if you do get a recipe figured out, here's the information on how to write it up to share with other users:
How to write & submit a tip for the community
Hannah your work here is inspirational!
I too would very much like to show and compare my metrics to a previous period. In my case, by quarter (and dynamically to show quarter to date). How much time do I spend looking into this, or are we ultimately hampered by ZD's restrictions? Alternatively, is there a suitable App/integration that would achieve a similar result?
Is someone able to tell me how that original query at the top displays the total count and the percentage difference underneath? I can't seem to figure it out with any of the options. Thank you!
Would love to have Hannah's recipe.
Please sign in to leave a comment.