Help with creating Explore report to measure % that took longer than XX time to reply toAnswered
We are looking to create a report for end user created tickets and the percentage of how many of those took LONGER than two business hours to reply to, for a given WORK week - is this possible?
Any insight is appreciated!
This is possible and there are several ways to do it.
For this example we need:
- a metric to count the number of tickets
- a calculated attribute that can help sort tickets above or below / equal to the 2 hours mark
- filter those tickets to get only the ones created by end-user
- use a Result Calculation Path to convert absolute values in percentages
This query must be created in the Support: Tickets dataset.
1. A metric for the number of ticketsWe can just use the basic metric
Ticketswith the COUNT operator
2. A calculated attribute that can help sort tickets above or below / equal to the 2 hours markYou can use the following formula to create the calculated attribute:
IF VALUE(First reply time - Business hours (hrs)) > 2 THEN "More than 2 business hours" ELIF VALUE(First reply time - Business hours (hrs)) > 0 AND VALUE(First reply time - Business hours (hrs)) <= 2 THEN "2 business hours or less" ENDIF
So this attribute allows us to categorize tickets in two categories:
- the ones with a first reply time equal or less than 2 hours
- the ones with a first reply time greater than 2 hours
You can find more information about creating calculated metrics and attributes in this article:
Once the attribute is added to the query, make sure to exclude
NULLvalue to exclude tickets that do not have a reply:
3. Filter those tickets to get only the ones created by end-userWe can use the attribute
Submitter roleand filter it only for end-user. The submitter is the user that created the ticket, regardless of if this user is the requester or not.
4. Use a Result Calculation Path to convert eh absolute value in percentagesFor this, we need to go in Result manipulation > Result path Calculation:
Pattern - % of total
Path - On rows
The query looks like this once completed:
Additionally, you can add a total in the Result Manipulation > Total > Grand Totals on Rows:
If you want the absolute value as well, you can add the
Ticketsmetric with the
D_COUNToperator. In this specific case, the
D_COUNToperator will return the same tickets as none of them can be repeated in the results:
Then you can use the regular time attribute to target the week you want. The metric itself counts only business hours so there is likely no need to tailor the attributes available to business hours. However, you can exclude days of the week if you want by using the time attribute of your choice that ends with
Day of week.
I hope this helps!
Please sign in to leave a comment.