Explore recipe: Reporting on the duration of fields

Return to top

47 Comments

  • Raul

    Hello,

    Regarding this mentioned limitation:

    '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 group that solves it? For our reporting, it is important to calculate all the time a ticket stays with a group as we have SLAs on the entire period a ticket is assigned, until resolution.

    Any advice on how to achieve this is much appreciated :)

    7
  • Prakriti

    Hi there,

     

    Is there a way to build these queries in business hours instead of calendar hours?

    I'm trying to modify this query "Calculating how long tickets sit with multiple support groups"

    5
  • Russell Milton

    Has there been any update to allow me to view the field changes time linked to the schedule of the ticket (i.e business hours)?

    4
  • PT Dadlani

    When will business hours be available or how can one calculate this using business hours?  Otherwise, this metric is more or less useless from a customer perspective.

    4
  • Dayana Flores

    Hello there, team!

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

    I appreciate your time and answers / advices to achieve this goal, because I have a lot of time trying to do this... Thanks!!! :)

    3
  • Dayana Flores

    Hello there, Comunity!

    Any has any comment could help me?

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

    I appreciate your time and answers / advices to achieve this goal, because I have a lot of time trying to do this... Thanks!!! :)

     

    2
  • Zaryab Khan

    I have been trying to compute Business hours a ticket spends in different/particular ticket group.

    I have tried this formula but with very marginal success (Computed only 3 tickets with non zero value and 6 tickets with an incorrect value of 0) the hours were computed in calendar hours which is not fit for reporting (have yet to come across a solution for Business hours) 

    IF ([Changes - Field name]="group_id") AND ([Changes - Previous value]="44xx704xxxxx")

    THEN VALUE(Field changes time (min))/60

    ENDIF

    1
  • Carlos Santos

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

    1
  • 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
  • Gab Guinto
    Zendesk Customer Care
    Hi PT Dadlani,

    I agree that it would be very helpful if the metric is also able to take into account business hours. This one's not yet in the roadmap, but I recommend that you start a thread about this here to engage with more users looking for a similar functionality. Thank you!
    1
  • Gab Guinto
    Zendesk Customer Care
    Hi Dayana,
     
    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.
    1
  • Jordan Means

    I am trying to create a metric that computes the difference between when a ticket is opened and the first time it goes Pending or On-Hold. First Reply Time and Time Pending don't fit that need.

    Any advice on how you'd create this?

    0
  • Andrew Chu

    I have a question related to "Calculating how long tickets sit with a support group" - I have tried the formula and it works for a single group, but now I want to calculate for more than one group, when I try to add further steps in the formula doesn't seem to work.

    Anyway to enhance this, or in another way, can we change the formula/set up in "Calculating how long tickets sit with multiple support groups" so that instead of a chart the results appear as table?

    0
  • 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
  • Thibaut

    Hi Yoram,

    I decided to create a ticket for you so I can look in details into your formula and query, you should receive a notification shortly about it.

    Thibaut | Customer Advocate | EMEA

    0
  • Rebecca Che

    Hi, we have a custom field with multiple custom ticket statuses (example: with the custom status we have Open/pending/hold, and under Open, there is sub category like troubleshooting, Remote session etc...)with a dropdown list, Now I'm trying to calculate the average time in each custom status, for example, how long time ticket sits in Open-troubleshooting. so the formula is created as below: 

    IF ([Changes - Field name] = "Custom status" AND [Changes - Previous value]= "Open::Troubleshooting")   
    THEN VALUE(Field changes time (min))/60   
    ENDIF

    but seems not giving correct value? could you help to check and advice?

    0
  • 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
  • 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?

    0
  • Christophe Tiraboschi
    Zendesk Customer Care

    Hi folks!

    Sandra de Jong,
    1. You could create a calculated attribute that returns the timestamp of when the ticket was assigned to this group:

    IF ([Changes - Field name]="group_id") AND ([Changes - Previous value]!=[Ticket group] AND [Changes - Previous value]!=NULL) 
    THEN [Update - Timestamp]
    ENDIF

    Then make a time difference between that timestamp and now in a calculated metric:

    DATE_DIFF(NOW(),[your attribute],"nb_of_hours")

    I would recommend testing the result of this metric on a couple of tickets before trusting it blindly to make sure it works as expected.

    You can find more information about the DATE_DIFF functions in this article if you want to adapt it to your needs:

    2. You can just use NULL to exclude or include all possible values. For instance:

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

    Basically, we measure the time for which the ticket field did not have a NULL value. Once again, please test this metric for a couple of tickets to make sure it works according to your expectations.

     

    Zaryab Khan, I cannot see a workaround for reporting on this during business hours. The initial metric Field changes time (min)) only uses calendar hours. We cannot even create a calculated metric as a workaround, since the functions that measure time cannot exclude specific ranges of hours:

    I am sorry I could not provide better news.

     

    Jordan Means, I think this is doable. You could create a calculated attribute using the DATE_FIRST function to capture the timestamp of the first time the status changes from Open to something else. Then, you could create a calculated metric that measures the time between the ticket creation and the timestamp returned by your attribute. Something like:

    DATE_DIFF([your calculated attribute],[Ticket created - Timestamp],"nb_of_minutes")

    You can find more information about the DATE_FIRST function and the DATE_DIFF functions in those articles:

    I hope this helps!

    0
  • Abdelhameed Khaled

    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
  • Tobias Testagent

    Hey,

    I have a question regarding the first report:

    "Calculating the average time a ticket is in each status through its lifecycle


    In this report, you'll discover the average time your solved tickets spent in each ticket status throughout their lifecycle."

    The formula e.g. behind the metric "AVG (On-Hold status time (hrs))" is:

    IF ([Changes - Field name] = "status" AND [Changes - Previous value]= "hold") THEN VALUE(Field changes time (min))/60 ENDIF 


    This formular means, that every status change from "on-hold" of all tickets is being used to calculate the metric, right?

    Lets assume we have two tickets in total:

    #1: was in status on-hold one time for 4h
    #2: was in status on-hold two times - first time 4h, second time 2h

    With this formular we use each status change on its own:

    4h+4h+2h / 3= 3,33h  AVG on-hold status

    But 3,33h is not the "the average time your solved tickets spent in" status on-hold, what the article says. Because the formular just ignores the "per ticket" and just calculated each status update.

    The actual time would be:

    #1: 4h
    #2: 4h + 2h = 6h

    -> 4h+6h / 2= 5h AVG on-hold status per ticket

    So imo you need first the SUM "on-hold status time" for each ticket and only then calculate the average.

    Do I have a thinking error here or is this article wrong?

    Thanks! =)

    0
  • Abdelhameed Khaled

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

    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
  • Alex Zheng
    Zendesk Customer Care
    Hey Rebecca,
     
    Let me open up a ticket with you to dive in further.
     
    Best regards,
    0
  • Rebecca Che

    thanks Dave. Tried below both but didn't give any value.

    IF ([Changes - Field name] = "Custom status")
    AND ([Changes - Previous value]= "active_troubleshooting")
    THEN VALUE(Field changes time (hrs))
    ENDIF

    Also tried 

    IF ([Changes - Field name] = "Custom status")
    AND ([Changes - Previous value] = Null )
    AND ([Changes - New value]= "active_troubleshooting")
    THEN VALUE(Field changes time (hrs))
    ENDIF

     

    Could you give some advice?Thanks

    0
  • Sandra de Jong

    Hi

    I'm trying to create 2 reports and i hope you could give me a suggestion.

    1: I want to know the duration of a ticket in the current group (since last change to this group)

    2: i want to know how long a custom field was filled. Difference with excamples on this page i do not have a specific data set i'm looking for. I need any data entered.

    hope someone could help me.

     

    0
  • Bill Cao

    Hi, Is possible to sum all durations of one field on a status to form a calculated attribute? i.e

    • Field 1 stays on status A for 5 minutes;
    • on status B for 10 mintues;
    • then back on status A for 15 mintus.

    I want to have an attribute which can show Field 1 on status A for 20 mintues total. Thanks

    0
  • Gab Guinto
    Zendesk Customer Care
    Hi Bill,

    The duration metrics for ticket statuses, like other custom calculations based on the metric Field changes time, are only able to track the time of previous statuses. It won't be able to calculate the time spent in the current status. 

    We don't an available recipe on this, but if you're comfortable using the different Explore functions (see Explore functions reference) in custom calculations, then may explore other workarounds, maybe try getting the timestamp of the most recent status change and then calculate using date_diff the duration from that timestamp until the current date/time. 
    0
  • Dane
    Zendesk Engineering
    Hi Andrew,
     
    Upon checking the format of the formula, it will only be possible for a single group. Even if you use "OR" statement it will just return all the values of the groups you will designate. The best option is to create multiple calculated metrics for each group.
     
    Hope this helps.
    0
  • 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. 
    0

Please sign in to leave a comment.

Powered by Zendesk