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
  • e-Services

    Hi,

     

    I am trying to measure the duration between the ticket creation and the time that the agent really started to work on the ticket (not assigned to the ticket).

    I have an alternative status field that my agents are updating depending on the ticket status and to achieve what I want, I need to measure the time it took to set the field value to "In Work"

    There are a few options here:

    • Move the ticket from "New" to "In Work"
    • Move the ticket from "New" to "Open" and then to "In work"

    I have tried the following query based on one of the posts above    but I can't get any value out of it.

    Is the query correct?

     

    IF ([Changes - Field name]="IntStatus")
    AND ([Changes - Previous value] = "New") OR ([Changes - Previous value] = "Open")
    AND ([Changes - New value] = "In Work")
    THEN VALUE(Field changes time (min))
    ENDIF

    Thanks

    Yoram 

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

    Hi Explore Community: 
      Am trying to create a report based on above instructions to determine time spent on a ticket for 2 support groups (A and B). Am facing issue of not able to display time spent in "B" support group. Screenshot for your reference:

    Scenario: The first Group A is the group whom received the ticket and escalated to Group B whom work and subsequently closed the case. I've verify the Group ID input are correct. Please advice:

    IF ([Changes - Previous value]="123") THEN "A"
    ELIF ([Changes - Previous value] ="456") THEN "B"
    ENDIF

     

    0
  • Gab Guinto
    Zendesk Customer Care

    Hi Simon,

    One limitation of this recipe is that it does not account for the time the ticket is sitting with the current group. It can only calculate the time the ticket was assigned to the previous groups (since the metric can only measure the duration when group_id value is changed). This is why you are not seeing the results for Group B in your graph – if that ticket was closed out while assigned to Group B (the current group), then you won't be able to calculate the time spent under that group using this recipe. You may need to build other custom metrics to measure the time from when a ticket was assigned to the current group until it was resolved. Sorry about this, Simon.

    -1
  • 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
  • Michael Bui

    I have a custom field that is date-based, I want to calculate from the time the ticket was created to when that field has a date entered. Essentially field change.

    This is the custom metric I'm using but I get no results

    IF ([Changes - Field name]="Installation Completed date" )
    AND ([Changes - Previous value] = NULL)
    THEN VALUE(Field changes time (min))
    ENDIF

     

     

    0
  • Darenne
    Zendesk Customer Care

    Hi Michael, 

    Due to the complexity of the issue, I've created a ticket for this concern and will give you an update on the ticket ID too. 

    Thank you for your kind understanding! 

    0
  • Mike Sanchez

    Any idea why I can't add Calculated attributes as a row? It appears under Metrics but neither in Columns or Rows.

    0
  • Diogo Maciel
    Zendesk Customer Care
    Hi Mike! By what you are describing, it seems you created it as a metric and not as an attribute. To learn more about the difference, please check the article below
     
    https://support.zendesk.com/hc/en-us/articles/4408824243738-Creating-standard-calculated-metrics-and-attributes
     
    I hope this helps!
    0
  • 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
  • Gab Guinto
    Zendesk Customer Care
    Hi Prakriti,
     
    Unfortunately, the calculations from custom metrics can only be in calendar hours. At this time, only the default ticket metrics such as first reply time are available in business hours. 
    -6
  • 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
  • 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
  • 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
  • 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
  • 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
  • Dave Dyson
    Hi Rebecca,
     
    I think as the article above states, you need to use the "value" (which in this context mean the tag) associated with your custom field option -- so rather than "Open::Troubleshooting", you'd put the tag association with that option there. Can you give that a try?
    -1
  • 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
  • Alex Zheng
    Zendesk Customer Care
    Hey Rebecca,
     
    Let me open up a ticket with you to dive in further.
     
    Best regards,
    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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • Tobias Tester

    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

Please sign in to leave a comment.

Powered by Zendesk