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

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

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)

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

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

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.

Hi Pedro Rodrigues
There is a difference between created tickets numbers by date based on created date and updated date. I tried to reconcile the numbers based on created date report(using support: tickets dataset and using support: update history dataset and your below metric but there is difference in numbers not significant but still don't understand why?
Is it because update dataset & tickets dataset time zone is different?

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

The provided query were not working