Explore recipe: Reporting on the duration of fields

Return to top


  • Tobias Rohling

    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)

  • Carlos Santos


    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.


  • 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. 
  • Tobias Rohling

    Hey Dane,

    any thought on my issue? =)

  • 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. 
  • Tobias Rohling

    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)

  • Martin Cubitt

    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?

  • 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. 

  • Bill Cao

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


  • Deiaa Eid

    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. 

  • Elaine
    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!
  • Carmelo Rigatuso

    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.



  • Carmelo Rigatuso

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

  • Rosie B.
    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! 
  • abdelhameed-khaled-origin

    Regarding this comment of Dayana Flores 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).


    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")

    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 



Please sign in to leave a comment.

Powered by Zendesk