If I am reading your question correctly, you want to know at some point each month, say at midnight on the first day of each month, how many tickets are currently open at that point in time. Is that correct?
For example, you started your data in mid-January, so January 1 shows 0.
During January, you open 100 tickets and close 10, so February 1 shows 90.
During February, you open 70 tickets and close 75, so March 1 shows 85 (90+70-75)
During March, you open 120 tickets and close 110, so April 1 shows 95 (85+120-110)
In Xtraction, when you set up a filter for Submitted During Last 13 Months, Xtraction queries the database for all tickets submitted during the last 13 months. The results include each ticket once. Then, the Time Component graphing the results counts each ticket in its appropriate time slice - each ticket is counted only once in only one time slice (a given month).
With what you are asking for, in the example above, a ticket opened in January and still open into April should be counted for Feb 1, Mar 1, AND Apr 1. Any tickets opened multiple months should be counted multiple times. Likewise, any tickets opened and closed in the same month would not count in your calculation.
So, the trick is, how to get each ticket counted for each interval (month, in your example) in which it applies. This requires getting each ticket to return "x" rows, 1 for each interval it belongs in. To accomplish this, you need to create what I'll call a calendar table in the database and populate it with records for each interval. In the past, I've created calendar tables that hold multiple different interval sets (months, weeks, days, etc.). Then, you would join this calendar table to the ticket table on the Open Date and use that.
The above is not a complete blueprint/how-to on building this solution. It is just a high-level description of the solution. There are a variety of details to consider (what info needs to be in the calendar table, where/how to map it into the data model, what filters are necessary in the dashboard to get the desired results). If you have a DBA/programmer/report writer type person that can work through your requirements and apply the solution I described above, it should work great. However, it is a bit much for me to try to describe in detail here in a forum post. I would usually recommend a short services engagement to assist with the solution. But, a "calendar table" can come in quite handy in a number of instances. These "backlog" requests are a perfect example (and I've seen this request many, many times over the years).
Thank Gregg. You have understood my requirements correctly, i just need to figure out the methodology with our DBA guys
I would also like to be able to report on backlog in this way, so if anyone else has managed this could you share the solution?