Pat Muirragui
New Member
- Joined
- Jul 31, 2007
- Messages
- 2
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.
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.