I am currently trying to track the total number of tickets available to our agents on any given day.
My thinking is that tickets available will be formed of the following components:
1) Backlog from previous day
2) Tickets created on the day
3) Tickets reopened on the day (but which were not created on the day, because those would be counted twice)
So this is what I did for each of those:
1) Created a custom metric to capture the Backlog at the end of the previous day, that is, the backlog that carries on to the next day, with the following formula:
SELECT # Backlog Tickets FOR PreviousPeriod(Date (Event))
2) Used the existing # Tickets Created metric
3) Created a custom metric which I believe captures the reopens of tickets not created on that day:
SELECT COUNT(Ticket Id, Ticket Text Field Change) WHERE Text Field =AND [Text Field] New Value IN ( , , ) AND [Text Field] Previous Value = AND Ticket Status <> AND Date (Ticket Created) <> Date (Event)
I then plotted them as follows:
# Tickets created
# Backlog Tickets (start of the day) - custom
# Reopened tickets (except created on day) - custom
# Tickets Solved (negative for plotting purposes)
Last 8 days
Here's a snapshot of the results:
So here comes my issue.
I expected the following to apply
Backlog (start of the day) = Backlog (start of the day) (day-1) + Tickets Created (day-1) + Reopened tickets (except created on day) (day-1) - Tickets Solved (day-1)
For the 2nd day on my plot, I thus expected the purple bit to be equal to: 309 + 334 + 76 - 345 = 374
But what I get is 296.
Any good soul would be able to give me a hand here? I imagine it's most likely an issue with my custom metric Reopened tickets (except created on day) - hence the title of my post - but so far I have been unable to figure out what.
Appreciate any help. Thanks