I've been running into an interesting issue that I'm hoping someone can suggest an approach for.
For context - I'm trying to calculate an average daily unique tickets worked where the following are true:
- Some tickets require multiple days to be solved, which will cause one ticket to contribute to the daily unique count of multiple different days.
- Our agents work a standard 5 day/week schedule.
Previously I was using the following to calculate an average, with an Update - Year filter to look at just this year's average:
D_COUNT(Tickets updated w/public comment)/DCOUNT_VALUES([Ticket updated - Date])
This gets me a total unique tickets worked for the year, over the number of dates an agent actually updated tickets (i.e., number of days they worked).
The problem I found with this query is that it is strictly counting unique ticket IDs which excludes tickets that were worked over multiple days and this ends up being significantly lower.
For instance, working with a "true" dataset counting unique tickets EACH day - I get something like the following:
Monday - 8 unique tickets
Tuesday - 7 unique tickets (one previously worked on Monday)
Wednesday - 9 unique tickets (two previously worked on Tuesday)
Thursday - 6 unique tickets
Friday - 10 unique tickets (three previously worked earlier in the week)
To average this set, I get 40 unique tickets over 5 days, average of 8. However, if I use the query above it would strip out the six tickets that were worked on multiple days, making it 34 over 5 days returning an average of 6.8.
What I want to figure out how to do is to calculate the unique tickets independent of eachother first, and then average these totals together. Using the Totals I can create an average when using:
D_COUNT(Tickets updated w/public comment)
and an Update - Date attribute column, but I can't figure out a way to then isolate that average or make it dynamic in a dashboard.
Is there a Results Calculation I can do to grab the column average and use that as a new metric? Or is there some clever query I can write that calculates each day's unique tickets first, and THEN divides by the total number of days to return a more accurate average?
Any advice I can get on this issue would be greatly appreciated!
Please sign in to leave a comment.