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

[Deepak Kumar5]

[brandimore]

Was this meant to be blank?

[Deepak Kumar5]

It is empty because i removed the name of report which i was testing on.

Do you want all the backlogs ticket right now OR count of backlog ticket on end of each month?

[brandimore]

End of each month. Run the backlog the 1st week of each month and you are looking at historical data for the previous months. That's why you can't filter out on the State or active fields.

I am looking at some of the indicators. There is an Incidents open and Incidents closed. I'm not quite getting it yet.  

I am surprised there's not more clear solutions on this. Is nobody reporting on backlog \ historical data?