Explore recipe: Reporting on the duration of fields

Return to top

23 Comments

  • Adrian Mitrea

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

    5
  • Yoram Dagan

    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
  • Russ 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)?

    2
  • 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"

    2
  • 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. 
    -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!!! :)

    1
  • 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!!! :)

     

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

    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.
    0
  • 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
    Zendesk Community Manager
    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

Please sign in to leave a comment.

Powered by Zendesk