Recent searches


No recent searches

Reporting on % of tickets solved within a set period of time

Answered


Posted Dec 17, 2021

I'm looking for help/a recipe for reporting on how many tickets (or the % of tickets) solved within 30 days and 60 days of being created. My organization has an expectation that 85% of tickets received will be solved within 30 days, and 98% will be solved within 60 days. I have looked into setting up SLAs to make this reporting possible but it doesn't seem to allow me to setup this specific SLA (i.e. ticket must be solved within 30 days of creation). 


1

24

24 comments

image avatar

ZZ Graeme Carmichael

Community Moderator

Mark

Start by making a custom attribute to calculate the days between the ticket created date and solved date.

 DATE_DIFF([Ticket solved - Date],[Ticket created - Date],"nb_of_days")

Then use this to create your brackets of tickets in a another new custom attribute.

IF [Days Between Created and Solved]<=30
THEN "30 Days and Under"
ELIF [Days Between Created and Solved]<=90
THEN "90 Days and Under"
ELSE "91 Days and Over"
ENDIF

... to give a report like this:

 

1


Thanks, Graeme! This did exactly what I was hoping for. Now, if I wanted the D_Count of tickets shown in the table for solved under 30 days expressed as a percent of total tickets solved, would that be easiest accomplished by creating another custom attribute based on the 2nd one?

0


image avatar

ZZ Graeme Carmichael

Community Moderator

Mark it is a little easier than that.

You can use result manipulation to add your total figures.

From you query, go to Results Manipulation>Result Path Calculation and use the settings:

This will give you:

To display both the ticket count and the percentages, you need a custom metric that is just a duplicate of our DCOUNT(Tickets)

....to give...

1


This is very helpful.

Is it possible to get days in business days only?

Thanks,

0


Hi Suhan,
 
That's going to depend on how you define "business days". Zendesk can give you first resolution time or full resolution time in business hours, and if you have a set number of business hours per "business day" (for example, if each business day is 8 hours), then you could just divide by that:
 
VALUE(Full resolution time - Business hours (hrs))/8
 
Then you could use that instead of the DATE_DIFF formula that calculates the number of days. However, this may give you undesirable results, if your business days vary from day to day, or if a ticket is created late on one business day and solved early the next, it would be counted as one business day instead of two -- so again, it depends how you want to define that.
 
My recommendation would be to just use the raw first or full resolution time in business hours, and base your time brackets on that, if that works with your business commitments.
 

0


I need a little help. Being a Beginner, I am trying to create an exact same report, however, I am getting an error and must be creating the attribute incorrectly. Would you know what is wrong? do I have to link both of them somehow?

0


image avatar

Alex Zheng

Zendesk Customer Care

Hey shilpi,
 
I can open up a ticket with you to investigate further.

2


yes pls

0


Hi ZD Team,

I have a similar use case in which I'm attempting to build a report that reflects % of tickets solved within 5 days of ticket creation date. 

I tried the steps outlined by Graeme, however, when attempting the second step outlined below, I received an error message. I'm wondering if there are been some change to Explore that have made this formula outdated. 

Then use this to create your brackets of tickets in a another new custom attribute.

IF [Days Between Created and Solved]<=30
THEN "30 Days and Under"
ELIF [Days Between Created and Solved]<=90
THEN "90 Days and Under"
ELSE "91 Days and Over"
ENDIF

 

0


image avatar

Noly Maron Unson

Zendesk Customer Care

Hi Valerie,

Try the formula below:

IF NUMBER([Days between Created and Solved])<=30
THEN "30 Days and Under"
ELIF NUMBER([Days between Created and Solved])<=90
THEN "90 Days and Under"
ELSE "91 Days and Over"
ENDIF

Hope this helps.

 

0


Hello,

I'm also working with % of tickets solved within a period of time.

In my case 24h and 12h but since a few months I have this error : the VALUE aggregator can't be used with calculated metrics.

Do you know what other aggregator I can use ?

0


image avatar

Alex Zheng

Zendesk Customer Care

Hey Damien,
 
The database level metric is Field changes time (mins) so you should be able to use VALUE(Field changes time(mins))<12*60)) instead.

Let me know if that doesn't work.

0


Hey All! 

Thanks for these instructions. Is there any help out there on how to make this into a KPI? 

Specially I only want to see the % of tickets solved within 30 days. 

Thank you! 

0


image avatar

Brandon (729)

Zendesk LuminaryUser Group LeaderThe Humblident Award - 2021Community Moderator

Hey Vanessa,

In this case, you would create a calculated metric that divides the number of tickets solved within 30 days by the total number of tickets, then multiplies this by 100 to get a percentage, displaying this metric as a KPI.  Hope this helps!

Brandon

0


Hello

Is there a way to do this but measuring hours rather than days? For example

4 hours and under

8 hours and under

9 hours and over

Thank you

0


image avatar

Brandon (729)

Zendesk LuminaryUser Group LeaderThe Humblident Award - 2021Community Moderator

Hey Clarice Francis

Yes, this should be doable using a combination of the original code and the calculated metrics

Then use this to create your brackets of tickets in a another new custom attribute.

IF [Hours Between Created and Solved]<=4
THEN "4 Hours and Under"
ELIF [Hours Between Created and Solved]<=8
THEN "8 Days and Under"
ELSE "9 Days and Over"
ENDIF

Hope this helps!

Brandon

0


Hi Brandon, thank you not sure if I've done something wrong, but I get the error "formula_checker_invalid_addition_types"

In the original "DATE_DIFF([Ticket solved - Date],[Ticket created - Date],"nb_of_days")" I changed it to nb_of_hours but it shows 0, 24, 48, 72 etc. should I change [Ticket solved - Date]? Unfortunately, [Ticket solved - Hour] did not work

0


image avatar

Brandon (729)

Zendesk LuminaryUser Group LeaderThe Humblident Award - 2021Community Moderator

Hey there Clarice,

Ahh good call!  In this case, I think you'd need to use the difference between the timestamps as your benchmark.  This should produce a result in nb_of_hours.  Let me know if that works for you!

Brandon

0


Hi Brandon,

Thank you for your help, unfortunately when I use the time stamp difference it gives me the error "formula_checker_invalid_addition_types" - please let me know if I am doing it wrong, I've pasted screen shots below too.

Thank you, 

Clarice

 

0


image avatar

Brandon (729)

Zendesk LuminaryUser Group LeaderThe Humblident Award - 2021Community Moderator

Hey Clarice Francis -

Try wrapping VALUE() around your attributes 

IF VALUE([Time stamp hour difference]) <= 4

0


Thank you for your help with this Brandon, and apologies for the back and forth. Unfortunately it did not recognise it, please see below

 

0


image avatar

Brandon (729)

Zendesk LuminaryUser Group LeaderThe Humblident Award - 2021Community Moderator

Hey Clarice Francis

No trouble at all!  I believe what needs to happen now is that you need to define [Time stamp hour difference] as a Metric in the dataset (opposed to an attribute).  Then you would build the attribute using the custom metric.  Give that a go and let me know if that solves for this issue.

Cheers,

Brandon

0


Hi,

 

I'm trying to do what Brandon Tidd suggested above (on February 20th)  but I'm not sure how to do it. Can I have more descriptive instructions on this step? 

I believe what needs to happen now is that you need to define [Time stamp hour difference] as a Metric in the dataset (opposed to an attribute).  Then you would build the attribute using the custom metric.  

 

Even better would be if I could get a formula for  these exact brackets:

1 day

2 to 5 days

6 to 30 days

31 to 60 days

61 to 90 days

91 to 120 days

121 to 150 days

151 to 180 days

> 180 days

 

Thank you,

0


image avatar

Brandon (729)

Zendesk LuminaryUser Group LeaderThe Humblident Award - 2021Community Moderator

Hey Laurie Schafer 

First, you'll need to create a standard calculated metric called “Timestamp day difference” with this formula:

DATE_DIFF([Ticket solved - Timestamp], [Ticket created - Timestamp], "nb_of_days")

Then here's the formula for the standard calculated attribute:

IF VALUE(Timestamp day difference) <= 1 
THEN "1 Day" 
ELIF (VALUE(Timestamp day difference) > 1 AND VALUE(Timestamp day difference) < 6) 
THEN "2 To 5 Days"
ELIF (VALUE(Timestamp day difference) > 5 AND VALUE(Timestamp day difference) < 31)
THEN "6 To 30 Days"
ELIF (VALUE(Timestamp day difference) > 30 AND VALUE(Tiemstamp day difference) < 61)
THEN "31 To 60 Days"
ELIF (VALUE(Timestamp day difference) > 60 AND VALUE(Timestamp day difference) < 91)
THEN "61 To 90 Days"
ELIF (VALUE(Timestamp day difference) > 90 AND VALUE(Timestamp day difference) < 121)
THEN "91 To 120 Days"
ELIF (VALUE(Timestamp day difference) > 120 AND VALUE(Timestamp day difference) < 151)
THEN "121 To 150 Days"
ELIF (VALUE(Timestamp day difference) > 150 AND VALUE(Timestamp day difference) < 181)
THEN "151 To 180 Days"
ELSE "> 180 Days"
ENDIF

 

Hope this helps!

 

Brandon

0


Please sign in to leave a comment.

Didn't find what you're looking for?

New post