Duration of Field Value (Between New & Solved Statuses)

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.

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