Show Tickets Created and Tickets Solved as two separate metrics in the same query

5 Comments

  • Pedro Rodrigues
    Community Moderator

    Hi Nadee Sharmila, the way I managed to obtain this was by adding a couple of standard calculated metrics in the Updates history dataset:

    Created:

    IF ([Changes - Field name]="status"
    AND [Changes - Previous value]=NULL
    AND [Changes - New value] != NULL)
    THEN [Update ticket ID]
    ENDIF

    Resolved:

    IF [Changes - Field name]="status" AND [Changes - Previous value]!="solved"
    AND ([Changes - New value]="solved" OR [Changes - New value]="closed")
    AND ([Ticket status - Unsorted] = "Solved" OR [Ticket status - Unsorted] = "Closed")
    AND ([Update - Timestamp]=[Ticket solved - Timestamp] 
    OR ABS(DATE_TO_TIMESTAMP([Update - Timestamp])
    -DATE_TO_TIMESTAMP([Ticket solved - Timestamp]) ) < 10)
    THEN [Update ID] 
    ENDIF

    You can then add both metrics to a query where in rows you can add Update - Year and Update - Month, for example.

    These formulas did the trick for me and return exact matches when comparing with # Created and # Solved tickets in the Tickets dataset.

    Also refer to this article

    0
  • David Stalker

    Hey there, Pedro!

    Can you clarify what this part is doing (specifically the OR part)? Is it limiting the selection to being within 10 days or something like that?

    AND ([Update - Timestamp]=[Ticket solved - Timestamp] 
    OR ABS(DATE_TO_TIMESTAMP([Update - Timestamp])
    -DATE_TO_TIMESTAMP([Ticket solved - Timestamp]) ) < 10)
    0
  • Pedro Rodrigues
    Community Moderator

    Hi David Stalker, so firstly we're saying that we only want to consider update timestamps that match resolution timestamps OR update timestamps that are 10 seconds apart from the resolution timestamps.

    This second part of the OR condition is related to something the Explore team detected in the Updates history dataset, as explained here:

    "We've encountered similar cases before where the recorded timestamps of the update and for the last solved date differ by 1 or 2 seconds (...) This formula should account for instances where there's a 1- or 2-second discrepancy between Update - Timestamp and Ticket solved - Timestamp."

    So I put an exaggerated 10-second value in the example above, but I was troubleshooting my own reports... 😀 You should be able to use the 2-second second discrepancy value Gab suggested without any problem (i.e. by changing "< 10" to "<= 2").

    0
  • David Stalker

    Thanks, Pedro! Great explanation! Wasn't sure what the unit was there, when dealing with timestamps. I was thinking it was days.

    0
  • Nadee Sharmila

    Hi Pedro, thank you so much. It is actually closer to what we're after so that's great. Still would be good to have this as an actual explore metric.

    1

Please sign in to leave a comment.

Powered by Zendesk