Insights recipe: Average resolution time without Pending or On-hold time

This recipe will instruct you on how to create a report to show the average ticket resolve time, excluding time spent in the Pending status. This recipe will also show you how to create a second version of the report that excludes time spent in the On-hold status.

Skill: Advanced

Time: 30 minutes

Ingredients:

One duplicated metric

Four custom metrics

Creating your average full resolution time metric

The first step in constructing your report is to change your metrics' original calculation to an average. You can do this by duplicating the metric, then editing the metric formula.

To change your metrics to an average

Click the Reporting icon ( ) in the agent interface, then open the Insights tab.

Click the GoodData link in the upper right corner.

Click Manage>Metrics.

Select the Full Resolution Time (hrs) [Mdn] metric.

Click the Duplicate button.

Click the Edit button.

In the Metric Editor, delete MEDIAN and enter AVG.

Enter a new name for your metric. This recipe uses Full Resolution Time (hrs) [Avg].

Click Save.​

Creating your average Pending and On-hold time metrics

The average Pending and On-hold time metrics are not included in Insights by default, so you will need to create custom metrics. For more information on creating custom metrics, see Creating custom metrics in Insights.

To create your average Pending time metric

In the What panel, click the (advanced) link next to Add new metric.

Select Custom metric.

Enter a name for your metric in the Name your metric text box. This example uses the name Pending Time (hrs) [Avg].

Enter the MAQL formula into the Editor pane. You cannot type in facts, metrics, or attributes, but instead must select them from the Elements drop-down list.

Repeat the same instructions as above, except using On Hold time (hrs) [Avg] and the On hold time in minutes fact.

Subtracting the Pending and On-hold time from the full resolution

In this section, you will create two final metrics to subtract On-hold time or Pending time from the overall full resolution time.

To create the full resolution time without Pending time

In the What panel, click the (advanced) link next to Add new metric.

Select Custom metric.

Enter a name for your metric in the Name your metric text box. This example uses the name Full Resolution Time - Pending time.

Enter the MAQL formula into the Editor pane. You cannot type in facts, metrics, or attributes, but instead must select them from the Elements drop-down list.

SELECT Full Resolution Time (hrs) [Avg] - Pending Time (hrs)[Avg]

Full Resolution Time (hrs) [Avg] andPending Time (hrs)[Avg] are your custom metrics created in the section above. They can be found in Metrics.

Click Add to add your metric.

To create the full resolution time without On-hold time

In the What panel, click the (advanced) link next to Add new metric.

Select Custom metric.

Enter a name for your metric in the Name your metric text box. This example uses the name Full Resolution Time-On-hold

Enter the MAQL formula into the Editor pane. You cannot type in facts, metrics, or attributes, but instead must select them from the Elements drop-down list.

SELECT Full Resolution Time (hrs) [Avg] - On Hold time (hrs)[Avg]

Full Resolution Time (hrs) [Avg] andOn Hold time (hrs)[Avg] are your custom metrics created in the section above. They can be found in Metrics.

Click Add to add your metric.

You can now create your reports. You can use the final custom metrics you created as your What, and any attribute as your How. It is recommended to use attributes like Ticket Group and Ticket Assignee.

a ticket goes through the normal process and gets marked "solved", but the customer comes back and it reopens now the initial time of 1st resolution has stopped.

The ticket then spends some more time in pending before getting resolved.

The issue I am now having is I can have a ticket that is getting a - (minus) hrs time as it has stopped counting the time to 1st resolution but pending time still includes anymore pending time after it has been reopened.

Any ideas how to avoid this? ideally needs to stop calculating pending time once the ticket was marked solved in the 1st instance?

I want to calculate this in Business hours. I see that there is already a metric available for Avg Full Res time [Biz hrs] in Hours available. Using this, can I create the Pending and On hold metrics as described above and subtract to get the Avg Full resolution time in business hours?

Nope, I followed this exact set of instructions. Strangely when I change the "how" to include ticket ID, I can export all tickets and see none with a negative count. that is the only change between the two reports and I get wildly different results on the same "what".

In order to troubleshoot this i've created a ticket for you and will be reaching out to you soon. There are many factors that could influence how a report renders so to really troubleshoot why you're consistently getting 0 values i'd like to take a look at your Insights report. You'll receive an email from me soon!

Thank you very much for this article. It's soooo helpful. I have the metrics set up, and now I am putting together a report. I am a bit confused about the impact of using dates of ticket creation vs. dates of ticket solved. What I want to see is the average time that it takes to fully resolve tickets that are created by day of week (do tickets created on Sunday have a longer resolve time, for example).

I have chosen:

What - Full - Pending (Average)

How - Ticket Assignee, and Day of Week Ticket Created

Filter - Ticket Assignee and Week (ticket solved)

My results don't look right. Is it possible to report on tickets that have been resolved/solved by the date that they are created?

Thanks for the question, Leigh. It seems that Fernando beat me to the punch. If you want to report on tickets that are only completely done, you will want to implement the filter described above. Thanks Fernando!

To add to my question above I put "Ticket Assignee" and "Week ticket created" as the HOW. As for the filter we use "Ticket created within 30 days". This is the result.

What I'm asking is, is it correct that the data that I'm getting is the average time an agent take to change a ticket status to solved minus the time the ticket is in pending or on hold for all tickets that is created within a certain week?

You are getting the correct data that you are looking for! The metric is giving Full Resolution Time minus Pending Time and On-Hold Time as you expect, and the attributes you've selected breaks that down per agent per week. The filter makes sure that it is only reporting weeks within the last 30 days. Great job!