How to create a Monthly Backlog report

[brandimore]

Hello

We need a monthly trending backlog report that shows incidents that are open for that point in time when the report is executed. If a ticket is open in Jan and Feb, then it would show for both months that it is open. See screenshot below.

I thought I would achieve this with the incident_metric table but there are a few issues. You can't aggregate by default on Incident Number so if you pull in New and In progress tickets, it will double count.   We can't use the Active is true either as Resolved tickets are active for 7 days after, so I believe that is a bit misleading. I think this is the table that can help

Sample result:

What I've tried so far.

One of the issues is that you can't count distinct, so you will get the ticket in the output twice which isn't good. And depending on how they log the ticket, through the front end or back end portal they sometimes go right to In progress.

Can someone suggest or chat about how they were able to get a monthly backlog report of open tickets?

Thanks

[brandimore]

You cannot filter out a state of a ticket. It will filter out all the previous months and this in turn will filter out tickets that WERE backlog for the previous months.

I am surprised that the Incident table would be a point of reference. I thought you would need to go more my direction, on the metric table and look at the update date for that point in time. This way you wont' lost your history.

Each month is a snapshot of time. That's a true backlog. You don't want it changing otherwise it's just an "As of when I run it" report. Maybe I'm missing something but I thought that was the big advantage of the incident_metric, so that you can view the history.

[Eric136]

visiting this ticket shows exactly what i am looking for..  i know it's old, but did you have a solution that you still use today?

[gyedwab]

This video shows two approaches to generating a backlog: one with a scheduled trend job to capture how many incidents are in the backlog OR a calculated approach by looking historically at (how many were opened) - (how many were closed) + (remainder from previous period):

https://www.youtube.com/watch?v=8gVQeegRWTE

[Kuldeep Kumavat]

Hi Amanda,

If you are using PA, then try creating an Indicator Source with the below conditions on the Incident Table.

This works correctly and shows the exact number of Open tickets at the end of each Month. For Historical data as well it displays the count correctly; but can get a bit confusing.

E.g. If an Incident was Closed in Feb, it means it was open by end of Jan. When you run historical Job to Collect Jan Month data, it will reflect the Incident under the Jan month as Open but with its current state which would be Closed. So, you will see the Incident as Open under Jan which is correct but with a state as Closed.

Hope this helps.

Regards,

Kuldeep

[brandimore]

Thanks so much Kuldeep. I think this is more what I'm looking for.

A question about running the historical job - can you do this at any time? Are there any downsides to this? We went live a few months ago, we ran it then and run the Daily job (daily of course).