IF ([Changes - Field name] = "Machine Progress") AND
([Changes - Previous value] = "build") OR ([Changes - New value] = "stop")
THEN VALUE(Field changes time (min)) ENDIF

• Sarah C

  • 13 septembre 2021 16:43

  Zendesk Customer Care

  Hi Russell!

  As simple as this request seems (tell me how long a ticket field was used in a ticket), it's actually quite tricky! If you are tracking a duration, it must have a start point and an end point, and it gets difficult when you need to track the current state.

  In these cases, you have to define your own end point, which gets very complicated - especially when you're using an intermittently-synced platform, such as Explore.

  So, let's first take a look at your current metric:

  IF ([Changes - Field name] = "Machine Progress") AND
  ([Changes - Previous value] = "build") OR ([Changes - New value] = "stop")
  THEN VALUE(Field changes time (min)) ENDIF

  'VALUE(Field changes time (min))' must have a start and end point. From your post, I understand that you're trying to find the value of time that a ticket stays in the Stop value. The above metric is tracking the time spent in Build or in the value that precedes when the field changes over to Stop. This is because 'VALUE(Field changes time (min))' needs a start and stop point, as it is a duration attribute.

  Let's take a look at one of our default duration metrics - New status time (min)

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

  It's pulling '[Changes - Previous value]= "new"' and reporting on the value of the previous value. 'VALUE(Field changes time (min))' will never be able to pull the current value of a field, as it requires a start and stop, and the current value doesn't have a defined stopping point.

  If you're looking to report on the time a field spent in Stop, you'd want to create a metric closer to this:

  IF ([Changes - Field name] = "Machine Progress" AND
  [Changes - Previous value] = "stop")
  THEN VALUE(Field changes time (min)) ENDIF

  That would pull up the value of the times that a ticket spent previously in Stop, but you're also looking to stop reporting when the ticket status changes to solved, correct? In this case, the above metric would not pull the values of a ticket that was solved during the stop value. Now is where it starts getting tricky.

  In this case, to create a duration, you will have to create your own ending timestamp. Luckily, since you're looking to stop reporting when the ticket is solved, you can just use '[Ticket solved - Timestamp]' for that.

  Now the real meat of the problem - how to pull the last time the value changed on Machine Progress. In this case, you're going to want to use DATE_LAST_FIX to find the last time the ticket changed to that value.

  While custom attributes and metrics are out of scope, I was super interested in figuring out how to do this, so this is the attribute I created when trying to get the last group change - obviously you may need to play around with this to customize it to your specific use case:

  IF ([Changes - Field name] = "group_id"
  AND [Changes - New value]!=NULL 
  AND DATE_LAST_FIX([Update - Timestamp], [Update ticket ID], [Changes - Field name])=[Update - Timestamp]) 
  THEN [Update - Timestamp]
  ENDIF

  In this case, this metric is designed to pull the timestamp when the group last changed. In your case, you may need to specify the value and the custom field.

  Following that, I created a metric using DATE_DIFF to pull the difference between the last update to the group and when the ticket was solved:

  DATE_DIFF([Ticket solved - Timestamp],[pull the last group update], "nb_of_minutes")

  Following that, you would just need to create a metric that added together your 'DATE_DIFF' metric and your previous value metric and make sure to use the SUM aggregator for that metric (just in case it moved multiple times to stop). One thing to keep in mind. If, at any time, the value is changed after the solve point, you will have a negative value for this metric. This is expected, as you are pulling the date difference from when the ticket is solved.

  For troubleshooting inconsistencies, I've always found it useful to pull up a specific ticket ID and divide it up by the update ID and add a filter for the '[Changes - Field name]' and also add '[Changes - previous value]' and [Changes - new value]' so that I can see what was changed and when it was. From there, you can add up the 'VALUE(Field changes time (min))' and compare it to your custom metric in a different query. It may take a bit to tweak this to what you're looking to create and while I was able to create something similar in my test account, this has not been stress-tested.

  In regards to being able to filter this by business hours, we don't have that capability in Explore, but I will mark this as product feedback for the product team. In addition, please add your use case and upvote on this Community thread requesting this feature.

• Russell Milton

  • 14 septembre 2021 09:49

  This is the way I managed to calculate this in the end, so the way I approached it is that ticket is only ever going to closed on a 'Stop' or 'Build' value in the 'Machine Progress' field. I used this final value to determine which metric calculations needed to be used to create the value I was looking for.

  So if the ticket was closed on a 'Build' value I know that it would of been solved on this value also. So, I would only need to measure the amount of time the ticket has been on 'Stop' as this value would only be used whilst the ticket was active:

  IF[Changes - Field name] = "Machine Progress" AND [Changes - Previous value] ="stop" AND [Changes - New value]="build" THEN VALUE(Field changes time (min)) ELSE 0 ENDIF

  However, if the ticket has been solved with the value 'Stop' this will include the duration of time the ticket status was Solved to Closed. So I calculated the "Active" ticket time (NEW,OPEN,PENDING,ON-HOLD) and then subtract the total ticket duration using the "Stop" and "Build" value durations and then add the Total Time on Stop (this will include the closed time).

  (Active Ticket - Total Ticket Time)  this will provide a Negative Number of Minutes (e.g the time spent between solved and Closed)
  + (Time Spent On Stop) the time spent between Solved & Closed will have the Value at '0' and anything additional will the time spent on with 'Stop' on active statuses.

  Maybe a very longwinded way of doing it but it's the only way I could figure it out.

  Here's all the metrics I used to calculate this:

  
  Top Level Metric

  IF [Machine Progress] = "Build" THEN SUM(Time Spent On Stop_v1)
  ELIF [Machine Progress] = "Stop" THEN (SUM(Ticket_Active_Time) - (SUM(Time spent on Build) + SUM(Time Spent On Stop_v1))) + SUM(Time Spent On Stop_v1) ELSE "0" ENDIF

  
  Metric 1 (Time Spent On Stop_v1)

  IF[Changes - Field name] = "Machine Progress" AND [Changes - Previous value] ="stop" AND [Changes - New value]="build" THEN VALUE(Field changes time (min)) ELSE 0 ENDIF

  Metric 2 (Ticket_Active_Time)

  IF [Changes - Field name] = "status" AND [Changes - New value] = "new" OR [Changes - New value] = "open" or [Changes - New value] = "hold" or [Changes - New value] = "pending" or [Changes - New value] = "solved"
  THEN VALUE(Field changes time (min)) ENDIF

  Metric 3 (Time spent on Build)

  IF [Changes - Field name] = "Machine Progress" AND [Changes - Previous value]= "build" AND [Changes - New value] = "stop"
  THEN VALUE(Field changes time (min)) ENDIF

• Sarah C

  • 23 septembre 2021 21:23

  Zendesk Customer Care

  Hi Russell,

  Thanks for showing your workaround and I'm glad to hear it works for your use case. However, for any others trying to do something similar to this who stumble upon this post, I would recommend finding the time the ticket was solved and subtracting that from the last time the field was changed. From there, you can use VALUE(Field changes time (min)) to pull the previous values and add that number to your first metric subtracting the ticket solve from the last time the field changed. Again, if, at any time, the value is changed after the solve point, you will have a negative value for this metric. This is expected, as you are pulling the date difference from when the ticket is solved.

   

Cette publication n’accepte pas de commentaire.