Average and Median Number of Tickets Created per HourAnswered
I am using the Zendesk Tickets dataset.
How can I create the average and median number of tickets created per hour metrics?
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!
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?
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.
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 😅
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.
@... 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?
- 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.
I'm having a similar issue to Jon – my average hourly counts are too high.
I think the problem with the metric @... 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:
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.
I am pulling data for the date range 10 May until 17 May for a specific assignee to get a sense of the number of tickets solved per hour and their average tickets solved per hour.
I have understood that the solved tickets is a count of all tickets solved during a particular hour, cumulative over the specified date range and that the average of those tickets/hour are provided in the AVG column.
Can someone confirm that I have understood the tickets solved - daily average metric correctly: I think that is showing what their average hourly performance is. In other words, if over three days an agent does 3 tickets at 7am, 5 tickets at 7am, and 4 tickets at 7AM, that AVG value in purple should be 4 - the average of 3, 5, 4. Is that correct?
That's right, that chart should show you the daily average of tickets solved for that hour. Similar to your sample calculation, essentially, Explore will count the sum of tickets that were solved at particular hour, and then divide that total by the count of unique 'Ticket solved - Date' values from that set of tickets.
Please sign in to leave a comment.