I have been trying to create a report that captures the first time a ticket goes Pending, On-Hold, or if neither of those happens, Solved.
My first attempt was to use the DATE_FIRST_FIX function to find the first update that met those conditions:
IF (([Changes - Field name]="status" or [Changes - Field name]="")
AND ([Changes - Previous value]="open" OR [Changes - Previous value]="new")
AND ([Changes - New value]="pending" OR [Changes - New value]="hold" OR [Changes - New value]="solved")
AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Changes - Field name],[Changes - Previous value],[Changes - New value])=[Update - Timestamp])
THEN [Update - Timestamp]
However, when digging into the results I found that this returned multiple results for the same ticket.
After running into that I tried another approach by separately computing the timestamps of each status change then comparing them. However, it is more and more looking like you cannot compare two calculated attribute as this simple if statement to calculate the earliest interaction on a single ticket is not working correctly.
IF DATE_LESS([Update to Hold Time],[Update to Solved Time])
then [Update to Hold Time]
else [Update to Solved Time]
Does anyone have any idea how to get either of these working? It is appearing impossible to compare two updates to each other in Explore.
Please sign in to leave a comment.