Help calculating New (Incoming) items and monthly Backlog.

[Pat Muirragui]

Would appreciate your help on this. I have attempted to create a Pivot table to report the number of New Customer tickets and the Backlog (or open tickets) at the end of each month. I am operating on the following data (as an example):

Ticket# Date_Open Date Closed Status Customer Priority
0001 02/01/07 02/06/07 Closed A 1
0002 02/02/07 02/10/07 Closed B 1
0003 02/03/07 05/03/07 Closed C 2
0005 02/05/07 05/05/07 Closed A 3
0006 02/10/07 06/02/07 Closed C 2
0007 02/15/07 Open A 2
0008 03/05/07 Open B 1
0009 03/15/07 05/06/07 Closed D 1
0010 05/10/07 06/05/07 Closed A 1

For this data, the results I am looking for for New and Backlog tickets would be as follows, at the end of each month:
Note: I can get the New tickets, that is easy, the backlog is the challenge.

Month New Tickets Backlog
February 6 4
March 2 6
April 0 6
May 1 4
June 0 2

The problem I am running into is that the month the ticket is created and resolved may two different time frames, so I cannot operate on the results of each with calculated fields. e.g., say Ticket # 0003 was opened in February, .. but closed in May.

 

[Pat Muirragui]

Was hoping for a quick answer, ... but no luck. I was able to find a solution, .. maybe not the best, but it works. It involves creating a pivot table with ResolvedDate on the Rows, Newly created on the Colums, and Count of Newly Created as the data. It creates a table that one can use to derive (through another table), the new tickets for each month (which is the row total, and the backlog) ... Would like to hear your ideas, ..
advanced thanks.

Pat