Question
When building a report I noticed some data is missing. How can I include 0 values in Explore queries?
Answer
There are a few ways to include 0 values in reports. The most popular are listed below. Use the one that suits your report configuration.
Time-based queries
If a report is time-based and there is no data for two NULL values of time attribute dimensions, like dates, weeks or months, you can use the Time axis result manipulation.
To enable the time axis
- In report builder, click Result Manipulation (
). - Select Time Axis from the list.
- Click Use time axis checkbox and then click Apply.
Reports with attributes on columns and rows
When a report has attributes on both columns and rows it sometimes has null values. This is especially visible in tables because some of the cells are empty. Use the Result metric calculation to replace these empty cells with 0 values.
For example, your report looks at the number of tickets created by year and group. It has Tickets in metrics, Ticket created - Year in columns, Ticket group in rows, and some cells are empty.
To create the result metric calculation
- In report builder, click Result Manipulation ().
- Select Result metric calculation from the list, then click Add a new metric.
- Enter a name for your result metric calculation.
- In the Formula box, from the Insert menu select the metric you need. In this example, it is COUNT(Tickets), then add "+0" to the formula. Your final formula will be:
COUNT(Tickets)+0
- When you are finished, click Clear used metric checkbox and then +Add.
18 Comments
Have you heard back Vladimir Petrushenka . I have the same issue where the last attribute in my report doesn't show up (year ticket created), so far this year it would be zero but it isn't showing 2021....0 my report is stopping at 2020. Thanks
HI
I have tried to create a new metric COUNT(Tickets)+0 and add it to the Metric of the query instead of having it in the "result metric calculation". and it didn't work
I have done so as I also wanted to use advanced formatting and by Zendesk support, advanced formatting will not work in you clear the used results.
Any Ideas?
Thanks
Yoram
hi @...
no, no one's responded. @... could you please advise us on this one when you have a minute? Or should we raise a support ticket ?
Many thanks,
Vladimir
Is the Time Axis feature not available for calculated attributes? I created a new "Ticket Created - Hour Ordered" attribute so I could rename the values (e.g. 0 > 12am, 1 > 1am, etc.) and reorder them. However, when I swap the standard "Ticket Created - Hour" attribute out for my new one, the Time Axis feature is no longer available under Results Manipulation. Thanks!
@..., @... I don't think there is a way to show the last data point if it has no data available. We will see how to improve this functionality when we will be working on this part of the Query building experience.
@... You need to use the Result metric calculation, not the Standard calculated metric. Result calculations are done after the data is pulled from the database that's why it allows showing some of the values as zeros.
@..., Yes the Time Axis feature is only available for the default time attributes.
Is there a way to show all values for all hours?
I have tried this approach, as well as the time axis "fix", but it still does not show any values for hours that have 0 tickets created at the start of the day (i.e. it would show empty values in-between hours that have tickets, but only starting at the first ticket of the day). For example:
First ticket comes in at 6AM, then no tickets until 9AM, hours 7 and 8 are still shown, however, hours 0, 1, 2, 3, 4, and 5 are not shown.
I just want every hour from 0-23 to show to keep comparisons between subgroups or other classifications easy to make, rather than having to work harder to establish which is the 5th hours of the day in 4 different graphs.
Thanks!
Hi,
Thank you for reaching out to Zendesk Support.
In regards to your concern, this article mentioned that the Time axis result manipulation is only visible if you are using a time-based attribute and when you add it to the Columns panel. The Time axis feature works only with a date field and will not work with 'Days of Week'.
Thank you!
Regards,
DJ Buenavista Jr. |
Customer Advocacy Specialist |
With table format, is there a way to get empty rows (having all 0s) to display?
I was able to get the cells having 0 to display using COUNT(tickets)+0 method above but it only works when some other cells of same row have a value
Otherwise if all cells of a row having 0, then that row does not display.
(e.g. an agent does not have any ticket in any status on the report)
Thanks in advanced
Hi Ryan,
Unfortunately, there is no native way to do that in Explore yet. Columns with NULL values in your query will not show by default. If an entire column is full of NULLs, it will always be hidden. Explore handles NULL values and zeros (0) differently.
Thanks Cheeny. Yeah I was wondering if there is away to show row having all NULL values. (At the top of this article it mentions as what you said and point to another question. And then on that question it points back to here)
Our goal is to show out ticket status count per agent daily (columns = ticket statuses, rows = agent list). And we want to show full list of agents, even if each agent has 0 for all ticket statuses on that day, to make it consistent for the system to process the CSV report file from Explore.
It's pretty inconvenient that Explore does not have this option available. Is there a way to add it to Explore in the future? It's common scenario, a few people would like to have it.
Hi Ryan,
We understand your need for this functionality so I am marking this ticket as Product Feedback. We truly value customer feedback and your voice and votes in the product feedback topics in the community help influence future Zendesk functionality.
@... Hi Cheeny! I join the request to have a possibility to show the row in the report despite it does not have any data in it. I can always hide not needed rows, so, no need for the system to do it without asking me :)
The link to Why do some columns not show in my query? doesn't work
I've shared this with our documentation team so they can dig into this further.
Cheers!
Just adding on here that having no way to get NULL values to show up is extremely inconvenient. Real example- I need a Guide report that shows me all published articles with their view count so we can see what is working and not. If I use metrics of published articles and view counts, articles with no views are excluded even using the calculated metric + 0 workaround noted here.
Turns out the Time Axis drops off at the point where all future columns are NULL, so this workaround doesn't actually work if you need every day of the month in the report, and the last few days of the month have 0s.
Ease
You might have success by adding in the "Published Articles" count. This would return a 1 for each article, even if the view count was zero. You could then hide that column even from the report, to keep it from being cluttered. I wasn't able to fully test this because I couldn't find a 0 view article (our own checks to make sure it looks good clearly get picked up), but I think it should do the trick. You can use Article Title if you prefer, it should work just as well as Article ID.
Hey CJ Johnson, so what you have is actually the exact report I started with.
If I do published articles as first metric and views as second, it excludes articles with 0 views. This is what got me down the rabbit hole because it doesn't really make sense to me that it would exclude 0 views with this setup.
If I remove the views metric and keep just published they all show up. I was using an article title filter against an article with 0 views as a second way to see if it was showing up after adjusting the metrics.
Please sign in to leave a comment.