Showing future dates in x-axis
I have a report where I want to show the current week's activity, but instead of fixed to 7 days back, the report should display the activity based of the first day of the week. To make the report look uniform and nice, I want the x-axis to be consistent.
Essentially, I want to display future dates in the x-axis of a graph (the date is the HOW in these reports). Any idea if this is possible?
I suspect this isn't possible because there's no data (=attribute reocords) for future dates, but I thought I'd ask.
This image explains it pretty well. The right hand side is what I'd like the report to look like day 2 of the week. I.e. the future dates should be blank and the x-axis consistently spaced.
-
Hi Joel! Insights can display future dates - there's even a floating range option for "days in the future." The problem, as you noted, is that Zendesk won't have any data for those dates.
By default, Insights won't include a column or row if it has no data. You can use IFNULL to force your metrics to return a real value anyway, which can make the columns appear in the report. For more information, GoodData has some great documentation about IFNULL.
Here's a simple example to get you started:
- SELECT IFNULL((SELECT # Tickets), 0) *1
This should count the number of tickets. If there are no tickets, it will return a 0 instead of a null value. The report would look like this:
- WHAT: # Tickets with IFNULL
- HOW: Date (Ticket Created)
- FILTER: List of Values - Week (Sun-Sat)/Year (Ticket Created) is this week
This report would show all seven days from Sunday to Saturday of the current week, regardless of when you view it or whether the days have data.
There are a few things to note with this workaround:
- This only works if you slice the report by a single attribute under HOW. If you add more attributes, it can't effectively replace the null value.
- You need to pick a replacement value that makes sense in context. In the example above, we would expect a 0 when no tickets were created. However, if a ticket never had an agent reply, then a 0 for first reply time would be misleading.
- Your screenshots show weeks from Wednesday through Tuesday. Insights slices weeks by Sun-Sat or Mon-Sun. There won't be an easy way to consistently start on Wednesday.
This is just a basic example. You'll need to thoroughly review and test your metrics before you use IFNULL in your own reports. However, this should get you closer to the results you describe.
Happy reporting!
-
Hi Amy
Thanks for sharing! I didn't know that, very useful tip!
I tried to apply it here to make the report I want and fail, as you warned, maybe it's not possible after all. In my case, my report has
WHAT
- #Public comments (based off events set)
- #Solved Tickets (based off events set)
HOW
- Date(Event)
- Updater
FILTER
- As you described, Week/YEAR
If I remove the Updater from HOW, and this metric for one of my metrics, I got the future dates displaying:
SELECT IFNULL((SELECT # Public Comments), 0)*1
But since removing Updater makes the report useless for my purpose (to show agent activities for the current week gradually populated as the week progresses), I tried adding back the Updater to the HOW.
At that point, it ends up like this, i.e. the future dates disappeares, like you suggested.
I did test adjusting both metrics (#Tickets Solved and #Public comments) to use the IFNULL... *1 syntax, but that didn't do it either.So I can't seem to create this report after all. Or might you know another tip that could resolve this?
-
Hi Joel!
The IFNULL (...) *1 syntax only works if you slice by a single attribute under HOW. It can't effectively replace null values if you have multiple attributes at the same time.
There won't be a way to display null values and updaters within the same report. At this point, I recommend creating a dashboard instead.
If you only need to see one updater at a time, you could put the basic report on your dashboard, then add a dashboard filter for the Updater.
If you'd like to compare updaters side by side, you could make separate versions of the report, then put them all on the dashboard. (You can add a report-level filter for the Updater; you just can't include the attribute under HOW.)
I hope this helps!
-
Alright Amy, thanks for getting back to me and confirming!
In my case I have more than 100 agents, so separate reports is not a viable option. I'll settle for my report that looks back 7 days here.
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
4 Kommentare