Pivot table
Posted by K. Robin on February 13, 2002 9:41 AM
I need to add in a Work on Hand calculated field in a pivot table for each month. The fields I have are:
CustomerName, OpenDate, CloseDate, Priority
I think the calculation should go something like this:
Feb2001 = All cases where open date < Feb 1, 2001 and the close date > Feb 1, 2001 (this is just a guess.....) but I need to do this for every month. I've tried many different ways with no success.
Any help would be appreciated.
Posted by Mark W. on February 13, 2002 10:01 AM
I don't understand...
Do you want to count the records that represent
'work on hand' for each month. And, your
definition of 'work on hand' is opened, but
not closed by the end of the month of OpenDate?
What fields are you including in your PivotTable/
Posted by K. Robin on February 13, 2002 11:28 AM
More explanation
My worksheet contains issues (cases) that have been reported by many different customers. I have created a pivot table to report how many cases arrived from each customer, each month, based on priority. I'd prefer to have the work-on-hand in the same report, but it doesn't have to be. Work on hand is the amount of cases still open on a specific date. For example, how many cases were still open for customer ABC on feb1, march1 and so on. The fields i have to work with are case, customer, open date, close date and priorty. Sample data is: 67, ABC, 01/30/01, 04/13/01, MJ. This case would be work on hand for feb1, march1 and april1.
Hope this makes sense.......
Posted by marbel on February 13, 2002 12:32 PM
Re: More explanation
No single pivot field will return all three values from your example, to the best of my knowledge. How did you do it in the separate file? It may require a bunch of calculated fields, one to check for each month.
mb
: Do you want to count the records that represent : 'work on hand' for each month. And, your : definition of 'work on hand' is opened, but : not closed by the end of the month of OpenDate? : What fields are you including in your PivotTable/
Posted by Mark W. on February 13, 2002 12:38 PM
Okay, using your sample data...
What date appears in your PivotTable? Is this date
a monthly "bucket"? How would this record be
considered by such a PivotTable?
: Do you want to count the records that represent : 'work on hand' for each month. And, your : definition of 'work on hand' is opened, but : not closed by the end of the month of OpenDate? : What fields are you including in your PivotTable/
Posted by Mark W. on February 13, 2002 1:35 PM
Here's an idea...
Why not create a PivotTable with 'open date' in
the ROW area, 'close date' in the COLUMN area,
and Count of 'case' in the DATA area? If desire,
you can make 'customer' and/or 'priority' a PAGE
field.
This provides a cross tabulation where any
entries in the "(blank)" column are not closed.
In fact you can type over the "(blank)" value
with a more descriptive term such as "Still Open"
or "Not Closed".
: Do you want to count the records that represent : 'work on hand' for each month. And, your : definition of 'work on hand' is opened, but : not closed by the end of the month of OpenDate? : What fields are you including in your PivotTable/
