Pivot getting messy

[theta]

Hi, I have a pivot where I need to getpivotdata based on 3 criteria :

1. Date (used as top axis)
2. Items code
3. Item status
4. Item supplier

The 4th item is the least important and used the least in analysis

My pivot looks like this :

----------------01/01/2010----01/02/2010----01/03/2010----01/04/2010
VALID
--RECEIVED
----SUPPLIER1
----SUPPLIER2
----SUPPLIER3
INVALID
--NO ID
----SUPPLIER1
----SUPPLIER7
--NO NAME
----SUPPLIER8
----SUPPLIER10
----SUPPLIER11

This allows me to produce all sorts of nice analysis using GETPIVOTDATA for the date / item / status.

How many valid 'x' did we have on a given date etc (ignoring supplier)

But now I need to produce another graph showing all INVALID items for a given supplier. So now I have been stuck using GETPIVOTDATA and listing all possible combo for INVALID :

(INVALID / NO ID) + (INVALID / NO NAME)

This is not ideal because a new reason code may appear meaning I would have to update all of my formulas

Any ideas, or better way of writing the GETPIVOT to summarise all data (or use an {array} for the INVALID status?)

 

[Andrew Poulsom]

Can't you create another pivot table based on the original and change the order of the row fields so that supplier is first in the order?

 

[theta]

That's what I was thinking, isn't that a bit messy though?

Although another pivot with the order changed would ensure all my problems are solved (including new status introduced in the future etc)

No way of using arrays in GETPIVOTDATA?

 

[Andrew Poulsom]

No way of using arrays in GETPIVOTDATA?

Not that I'm aware of.

 

[theta]

Thanks Andrew

Works better this way and is more scalable. Just copied the pivot tab so they will both be running off the same pivotcache.

Fixed all my probs...