Excel Pivot Table Calculated Field

[gdev]

Hi Everyone,

Please assist!

I want my pivot table to calculate the % change of the item counts on a day by day basis.

I believe this is the formula I need for the calulated field:
(current dte itm ct - yesterday's dte itm count)/current dte item ct)*100

How can I make this work with an Excel pivot table?



Any suggestions.


item# 12/12/04 12/13/04 %Change 12/14/04 %Change

item1 100 110 10% 105 -5%
item2 24 35 40% 100 185%
item3 17 17 0 50 194%

Thanks
Gwen

 

[vaneagle]

Hi Gwen,

Make sure your pivot table toolbar is showing. Goto Pivot Table-->Formulas-->Calculated Field. It then pops up a dialog box. Give it a name (no Spaces), then in the formula box enter your fields for your calculation... I am guessing something like this (current dte itm ct - yesterday's dte itm count)/current dte item ct)*100

hope this helps....

vaneagle

 

[gdev]

Thanks

The pivot table column area consist of one date field. The output is all of the dates in the table.
How do I create the calculated % change field using the date field?
Do I need to go back to my query and create some other field to make this work?

Please help

Thanks

Gwen

 

[01Patrik]

A calculated FIELD can only be used for calculations between different fields (like DATE and QUANTITY). If you want to calculate within a field ( one DATE vs another DATE), then you must use calculated ITEM. Check the help for details, it's pretty good.



// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[gdev]

Thanks everyone for all your help.


Gwen