Help with creating Explore report to measure % that took longer than XX time to reply to

Hi Albert,
 
This is possible and there are several ways to do it.
 
For this example we need:

1. a metric to count the number of tickets
2. a calculated attribute that can help sort tickets above or below / equal to the 2 hours mark
3. filter those tickets to get only the ones created by end-user
4. 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 tickets

We can just use the basic metric Tickets with the COUNT operator
 

2. A calculated attribute that can help sort tickets above or below / equal to the 2 hours mark

You 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:

• Creating standard calculated metrics and attributes

 
Once the attribute is added to the query, make sure to exclude NULL value to exclude tickets that do not have a reply:
 

 

3. Filter those tickets to get only the ones created by end-user

We can use the attribute Submitter role and 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 percentages

For this, we need to go in Result manipulation > Result path Calculation:
 

 
There, select:
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 Tickets metric with the D_COUNT operator. In this specific case, the COUNT and D_COUNT operator 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!