I've been trying to figure out the total amount of time a ticket has spent with a field value of 'Stop' whilst in New/Open/Pending/On-Hold/Solved Statuses.
So using the ticket - Updates dataset I've tried the following calculated metric
IF ([Changes - Field name] = "Machine Progress") AND
([Changes - Previous value] = "build") OR ([Changes - New value] = "stop")
THEN VALUE(Field changes time (min)) ENDIF
but that has only given me the number of minutes that the ticket is in this status until it is closed. (I need to stop counting this once the ticket has reached 'Solved')
Here are the ticket updates.
I've tried looking at this from multiple angles and I'm not sure whether I'm not looking at the correct functions or maybe a missing some information.
I've tried using DATE_DIFF functions but I'm unsure that this would allow me to capture the total time as this value can change multiple times throughout the Lifecyle of a ticket. (this and the results appear on differing rows so it' won't calculate this at all.)
Also, if it was possible to get this information in business hours that would be fantastic.
Please sign in to leave a comment.