Comparing the number of Open tickets at the start & end of the day

Hi Emily,
 

If you only need to check the ticket status at specific times—such as “Open” at 8 AM and 11 PM on the same day—I think this can be done by creating a custom calculated metric with an advanced date filter.
 

I created sample formulas for a calculated metric that checks if tickets are “Open” at those times without tracking all status change events on the ticket.

Formula for “As of 8am”:
IF(DATE_LESS_OR_EQUAL([Ticket created - Timestamp],DATE_ADD(DATE_ADD(TODAY(),"day",-1),"hour",8))
AND [Ticket status - Unsorted] = "Open")
THEN [Ticket ID]
ENDIF

Formula for “As of 11pm”:

IF(DATE_LESS_OR_EQUAL([Ticket created - Timestamp],DATE_ADD(DATE_ADD(TODAY(),"day",-1),"hour",23))
AND [Ticket status - Unsorted] = "Open")
THEN [Ticket ID]
ENDIF

To capture all the tickets and check if they are in the “Open” status,  please add a date filter on the ticket creation date. Use the advanced option to select the date range from “All history” to “Yesterday.”
 

Here is a sample result of this setup:
 

This formula and date filter are designed to capture tickets created yesterday, assuming you will generate this report the next day. For example, if today is July 11, you’ll see the open tickets as of 8 AM and 11 PM on July 10. If you want to check for today instead, remove DATE_ADD(TODAY(), "day", -1) from the formula and change the date filter from “All history” to “Today.”

You can use this formula with either the ‘Support - Tickets’ or ‘Support - Updates History’ datasets.

Please note, I haven’t fully tested this and can’t guarantee that it will work perfectly, but I believe it’s worth trying to see if it provides the data you need.
 

If this doesn’t give you the data you want, or something similar, you might want to explore the “Backlog history dataset.” However, this dataset has some limitations as it only captures a daily backlog snapshot of your account. You may want to compare the backlog for open tickets between yesterday and today.

I hope this helps. Thank you!