# 7 Comments

I'm having a similar issue to Jon – my average hourly counts are too high.

I think the problem with the metric Taylor Bowser mentioned is that it doesn't account for zeros. For example, if I want to know the average number of tickets submitted at 1am last week, and the data looks like this:
M: 0
T: 0
W: 1
T: 0
F: 0
Sa: 0
Su: 0

The true average is 1/7, or .14, but the default "daily average" metric will tell me the average is 1,  because the ticket count is 1, and the count of values for created date is also 1, because there is only one ticket with a. value to count. The denominator needs to include the dates that have no tickets, but it doesn't. I don't know how to create a metric that includes the days with no data. It seems like a pretty simple ask. I need help.

Zendesk team member

Hi Chen,

Thanks for your question! Can you clarify you were looking for a query that showed both AVG and MED tickets created per hour? If so I made a metric using Totals Result Manipulations to show both Median and Average:

Metric: Count(Tickets), Columns: Ticket created - Day of week, Rows: Ticket created - Hour.

Under result manipulation > Totals > Advanced > Add New Total (Attribute:All Columns, Aggregator: AVG) > Add New Total (Attribute:All Columns, Aggregator: MED).

Hopefully I got the right idea here, but please let us know if you have any other questions!

Hi Gentry,

Thank you for your answer.

My view looks like this:

I created a calculated attribute for the hour of the day (Columns)  and I am counting the number of tickets.

I tried to use the path you mentioned: Under result manipulation > Totals > Advanced > Add New Total (Attribute:All Columns, Aggregator: AVG) > Add New Total (Attribute:All Columns, Aggregator: MED).

but I can't see the relevant option :

Any idea why?

Thank you

Zendesk Customer Advocate

Hey there Chen!

I'm not Gentry, but I can tell you the reason you are not seeing this table as Gentry described is due to the Visualization type of your query. Gentry is utilizing a Table, you would need to do the same on your end in order to see the advanced tab described.

Thanks!

I'm trying to create a query in Explore that just shows the number of average daily tickets created over the last year.

So far I created the query below and added a Total Result Manipulation > all columns > average. That gives me a column with the daily average over a specified time period (which is great!), but I just want a text box that spits out a single number without me having to hide a bajillion columns from the past year's daily data.

Does that make sense? I'm trying my best to do my due diligence for Explore 😅

Zendesk Customer Advocate

Hi Pam,

There is actually a default metric within the Support: Tickets dataset that calculates the daily average of tickets created:

This will return a single value reflecting the average across all dates. You can then add a filter for Ticket Created - Date to filter the results to this year.

Hope this helps!

I'm trying to understand avg ticket per hour for each day as well.

Taylor Bowser can you provide any clarity or link to an explanation on why the daily avg seems to default to 1 no matter the numbers. Is there a way to change that when there are low frequencies to get a decimal less than 1?

For example:

- Total tickets will be between 1 and 6 (7 days in the week) for a specific time period in a given day

- Daily avg metric your referenced will always say 1 when clearly it is less than 1 (let's say there were 2 tickets total in hour 22 so the actual avg should be 2/7 = .28 tickets per day)

I've tried to manipulate this in different ways but think I am missing something.

Please sign in to leave a comment.