• Pedro Rodrigues

  • May 05, 2022 13:36
  • Edited

  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

• David Stalker

  • May 05, 2022 14:19

  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)

• Pedro Rodrigues

  • May 05, 2022 14:45

  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").

• David Stalker

  • May 05, 2022 14:47

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

• Nadee Sharmila

  • May 06, 2022 00:34

  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.

Please sign in to leave a comment.