Recent searches


No recent searches

Explore recipe: Reporting on the duration of fields



image avatar

Rob Stack

Zendesk Documentation Team

Edited Jun 21, 2024


1

55

55 comments

so this would look right for me - am I correct?

(In my example I need the time per month, this isn't important)

1. Calculate the total on-hold time for each ticket (red)
2. Calculate the average of these values (blue)

0


Hi.

From the comments, I gather the answer to this question is still NO but just in case something changed recently here goes:

Is it possible to generate a report that shows how much time a ticket has been assigned to each of my teams from creation to closure? 

Thank you.

 

1


image avatar

Dane

Zendesk Engineering

Hi Carlos,

It's not readily available. However, I'm thinking that it's possible through some form of customization with the Time Tracking App. Unfortunately, this is outside our scope and let's hope someone found a way to go about this. 

-1


Hey Dane,

any thought on my issue? =)

0


image avatar

Dane

Zendesk Engineering

Hi Tobias,

When it comes to calculating the AVE your approach is correct. It will add up all the values in the columns and get the average of it. 

0


Hey Dane,

thanks a lot for confirming! :) 

I would suggest to change the article - cause it says:

"This gives you the tools to answer some important business questions like:
What's the average time a ticket sits in each status through its lifecycle?"

So everyone using this article is getting the wrong results for this question mentioned (what the whole article is about)


1


I am really happy to see that Zendesk allow custom statuses (albeit linked to one of the core statuses). 

However, I am unable to find a way to report on the time that a ticket is at a custom status, since in order to calculate time between events I need to use the Updates History dataset, yet custom statuses are not available in the dataset, only in the Support Tickets dataset.

 

How do Zendesk suggest getting this information?

0


image avatar

Zsa Trias

Zendesk Customer Care

Hello Martin,

Unfortunately, at this time, custom statuses can only be reported in the Tickets dataset. 

For reference: How can I report on tickets with a custom ticket status?

These attributes are only available within the Tickets dataset, and are not available within the SLA or Updates History dataset. It is not currently possible to report on the duration of tickets within custom statuses using pre-built metrics. 

0


This "VALUE(Field changes time (hrs))" suddently does NOT work for me. Am I doing something wrong, or does it have a replacement? Thanks

 

0


Hi Dane
I have a question regarding the duration report. I would like to create a report that calculates the time spent from when a ticket is created until it reaches the 'On-Hold' status. This report should only include tickets that are in the 'On-Hold' status. It should analyze the history of all 'On-Hold' tickets and calculate the time from ticket creation to 'On-Hold' status.
Also need to calculate the time duration between ticket created to change the ticket type to Problem. 

0


image avatar

Elaine

Zendesk Customer Care

Hi Bill,
 
That's because you are using the VALUE aggregator. It was mentioned in this article that VALUE aggregator can still be used with the database-level metrics and it is not allowed to be used with the calculated metrics, which was always the case as explained in the article you mentioned. See Troubleshooting errors in Explore formulas we have listed a few different ways to avoid the syntax error with the VALUE aggregator. 
 
Instead of using VALUE(Field changes time (hrs)) it's recommended to utilize VALUE(Field changes time (min))/60/24 because the database-level metric is in minutes. This adjustment should provide more accurate results. 

Hope this clarification is helpful!

1


I can get the duration of the custom fields to work, but now I need to also know who the assignee was during that time. I have customer status for an order flow that goes through a few stages. What I can't seem to work out is who specifically worked that ticket during that status. Since the status field changes and the ticket assignee changes are in the same event, the next agent gets hit with the previous custom status duration. Ex.

  • New ticket - unassigned, custom status is "Not Started"
  • Bob picks up the ticket 5 mins later, status is open, custom status is "in progress"
  • Report shows that Bob spent 5 mins in "not started" status, which is wrong. This gets repeated with every change.

I tried getting the previous value of the assignee, (which is a pain itself) but the ticket doesn't always change hands when the custom status changes.

Any help or tips would be appreciated.

 

 

0


For those of you that were looking for measuring time in business hours, I created a feedback  post here. Please comment and upvote!

1


image avatar

Rosie

Zendesk Customer Care

Hi Carmelo, 
 
Thank you so much for your comprehensive feedback and reasoning. Also for taking your time to share all this information with us. Our product team have been informed and they will continue monitoring any feedback in this area. If more people share similar feedback like you, there's a possibility that it will be added to later updates.
 
Thank you! 
 

0


Regarding this comment of Dayana and answer of @Gab guinto 


Comment :
You mentioned: 'This query measures the time a ticket spent in a group before being moved to another group; it does not include the time a ticket spent in a group before it was solved.'

Is there a way to adjust the formula to include also the time spent in the last group that solves it? For our reporting, it is important to calculate all the time by group in the lifecycle of the ticket and I'm missing the last one (the time of the group that solved the ticket).

Answer:

Since the recipe uses the field changes attributes, it's not possible to measure the time spent with the current or final group with the same metric/formula.
You may try to build separate attributes to get the timestamp of the last group assignment (Working with earliest and latest date functions) and create a metric to calculate the duration between that timestamp and resolution.


Now I tried to do this Metric to get the duration between last group assignment and solve date to add it on the calculation of counting how long ticket stay with each group but it keep showing me the last update timestamp whatever it is not the group assignment timestamp and there are some corner cases such as if the ticket reopened and solved again or the group is changed after the ticket is solved 

IF ([Changes - Field name]="group_id")  
THEN DATE_DIFF(DATE_LAST([Ticket solved - Timestamp]) ,[Update - Timestamp] ,"nb_of_minutes")
ENDIF

The metric gives different results when I change the aggregator function . I want to get the average for each group but when I choose the average it give the average for the ticket itself from first group to solve timestamp then 2nd group to solve timestamp then 3rd and etc ..

I could use any help on that 

 

0


In this Point :

Calculating how long tickets sit with their last support group  

How could I hide calculate the average and hide all rows to show just the average of them which is the last row 

0


image avatar

Alex Zheng

Zendesk Customer Care

Hey Adbelhameed,
 
If you are looking to hide rows or columns in your report I would take a look at the article here which goes over a few options that you have for doing so.
 
Let me know if you have any further questions.

0


Is there a way to distinguish between when a ticket has been 'Solved' and when a ticket has been 'Closed'?
I'm also finding that the 'Capturing the last update' attribute only returns one value and can't be split by Ticket ID

0


How would you modify the formula to show business hours and not the total time spent between changes?

0


image avatar

Alex Zheng

Zendesk Customer Care

Hey Jonathan,
 
Unfortunately, business hours calculations are limited to the default ones available. Any custom formulas will not take into account business hours, you can read the article here for further information.

1


I followed the steps for “Calculating how long tickets sit with their last support group” but would like to remove any time the ticket was “pending” or “on-hold” from the resolution time as our agents for time spent waiting for others. Is there a way to update the calculation to handle this?

0


Hello, 

 

I am trying to determine the duration during which a text field was NULL. I have tried both of the following formulas as attributes and as metrics on the “Updates History” dataset, and no combination has returned anything. Text field name is “Jira ID”.

IF ([Changes - Field name]="Jira ID")
AND ([Changes - Previous value] = "NULL") AND ([Changes - New value] != "NULL")
THEN VALUE(Field changes time (min))
ENDIF

IF ([Changes - Field name]="Jira ID" )
AND ([Changes - Previous value] = “NULL”)
THEN VALUE(Field changes time (min))
ENDIF

Can anyone advise? Thank you in advance.

Alex

0


the formula is not working at all, No matter what I do I get “There's an issue with the formula…”

0


I saw several comments trying to get the time in each group including the last group, but still no solutions.  We are looking for the same but with assignee.  Hoping for a solution in 2025.

0


Hi 

 

We have a custom ticket field for Jira Status which updates as the Jira ticket moves through it's lifecycle. 

 

I am trying to build a report that shows me the average amount of time tickets spend in each Jira Status to help me to identify any areas to improve. Does anybody have any suggestions on how I can achieve this? Reporting on the field change time does not work because that is excluding tickets that have not changed status, i.e. have been in Jira Status = Backlog since being escalated.

 

Thanks

 

James

0


Please sign in to leave a comment.