G
GreyPilgrim
Only just moved on to Excel 2000 and I'm having a problem wit
calculated fields vanishing from Pivot Tables / graphs. I've attached
link to a very simplified example of my pivot data to help illustrate
Each record represents a persons daily attendance, and can be in
number of states (I've used four in the example, Sick, Meetings, et
etc etc). A '1' in the relevant column indicates the state (others wil
default to '0').
Aim of the pivot is the give an overall percentage for each person ove
various time periods. To help with the calculation each record also ha
a TOTALCOUNTER field with a value '1' in it (which act as th
denominator part of the equation when I sum it).
I've build calculated fields for each of the four states along th
lines of: PERCENTSICK = SICK / TOTALCOUNTER.
These work out fine and the summary percentages give me the numbers
want. however If I deselect any of the data elements from the pivo
drop down box, I can't reselect them.
I hope I'm explaining this ok, or not waffling. The example will sho
what I mean more clearly - If you deselect %SICK from the DATA dro
down list, you can't select it again. Is there any way to stop thi
happening?
http://www.flamingmango.com/tempdump/CalcFieldExample.xls
Real version is a lot more involved, and using the STATE / TOTALCOUNTE
calculated field method is the only way it's going to work, but I nee
the users to be able to select and deselect the different states a
will. Any help (even if its just to confirm it's not possible) would b
welcome.
Cheers
Le
calculated fields vanishing from Pivot Tables / graphs. I've attached
link to a very simplified example of my pivot data to help illustrate
Each record represents a persons daily attendance, and can be in
number of states (I've used four in the example, Sick, Meetings, et
etc etc). A '1' in the relevant column indicates the state (others wil
default to '0').
Aim of the pivot is the give an overall percentage for each person ove
various time periods. To help with the calculation each record also ha
a TOTALCOUNTER field with a value '1' in it (which act as th
denominator part of the equation when I sum it).
I've build calculated fields for each of the four states along th
lines of: PERCENTSICK = SICK / TOTALCOUNTER.
These work out fine and the summary percentages give me the numbers
want. however If I deselect any of the data elements from the pivo
drop down box, I can't reselect them.
I hope I'm explaining this ok, or not waffling. The example will sho
what I mean more clearly - If you deselect %SICK from the DATA dro
down list, you can't select it again. Is there any way to stop thi
happening?
http://www.flamingmango.com/tempdump/CalcFieldExample.xls
Real version is a lot more involved, and using the STATE / TOTALCOUNTE
calculated field method is the only way it's going to work, but I nee
the users to be able to select and deselect the different states a
will. Any help (even if its just to confirm it's not possible) would b
welcome.
Cheers
Le