calculating differences in a pivot table
 

I use a pivot table to show sales by customer. i am looking at sales
for year 2006 and 2007. i placed the customer name field in the row
label, and the sales amount field in the values area of the pivot
table. i placed the year field in the column label to look at the 2
years seperately. is there a way to use the pivot table to calculate
the difference between the 2 years for each customer rather than
calculating the grand total for the 2 years for each customer.
thanks.
T

calculating differences in a pivot table
 

Hi

In your PT, right click and choose Table Options. De-select Grand Totals by
Row.
Right click again and choose Pivot Table WizardLayoutdrag Sales amount to
the data area a second time.
Double click on the Sum of Sales2 icon and from the dropdown Show Data
aschoose Difference FromBase Field YearBase Item 2006OK.OK.Finish.

On the PT, drag the Data button and drop on Total and you will see the
values side by side.


--
Regards
Roger Govier



wrote in message
ups.com...
I use a pivot table to show sales by customer. i am looking at sales
for year 2006 and 2007. i placed the customer name field in the row
label, and the sales amount field in the values area of the pivot
table. i placed the year field in the column label to look at the 2
years seperately. is there a way to use the pivot table to calculate
the difference between the 2 years for each customer rather than
calculating the grand total for the 2 years for each customer.
thanks.
T

calculating differences in a pivot table
 

On Sep 26, 6:17 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

In your PT, right click and choose Table Options. De-select Grand Totals by
Row.
Right click again and choose Pivot Table WizardLayoutdrag Sales amount to
the data area a second time.
Double click on the Sum of Sales2 icon and from the dropdown Show Data
aschoose Difference FromBase Field YearBase Item 2006OK.OK.Finish.

On the PT, drag the Data button and drop on Total and you will see the
values side by side.

--
Regards
Roger Govier

wrote in message

ups.com...



I use a pivot table to show sales by customer. i am looking at sales
for year 2006 and 2007. i placed the customer name field in the row
label, and the sales amount field in the values area of the pivot
table. i placed the year field in the column label to look at the 2
years seperately. is there a way to use the pivot table to calculate
the difference between the 2 years for each customer rather than
calculating the grand total for the 2 years for each customer.
thanks.
T- Hide quoted text -

- Show quoted text -

Thanks. I removed the grand totals for each row and i added a 2nd
field for sum of sales amt. then i changed the data for the 2nd sales
amt field to be displayed as a difference from base yr 2006 like you
said above. I get #N/A as the resulting value. Also, i am not sure
it makes a difference, but i am using Excel '07. Thanks.
T

calculating differences in a pivot table
 

Hi

No, it works in XL2007 as well.
If you want, send me a copy of your file and I will set it up for you.
To send direct, email to
roger at technology4u dot co dot uk

Do the obvious with at and dot in the email address.

--
Regards
Roger Govier



wrote in message
ups.com...
On Sep 26, 6:17 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

In your PT, right click and choose Table Options. De-select Grand Totals
by
Row.
Right click again and choose Pivot Table WizardLayoutdrag Sales amount
to
the data area a second time.
Double click on the Sum of Sales2 icon and from the dropdown Show Data
aschoose Difference FromBase Field YearBase Item 2006OK.OK.Finish.

On the PT, drag the Data button and drop on Total and you will see the
values side by side.

--
Regards
Roger Govier

wrote in message

ups.com...



I use a pivot table to show sales by customer. i am looking at sales
for year 2006 and 2007. i placed the customer name field in the row
label, and the sales amount field in the values area of the pivot
table. i placed the year field in the column label to look at the 2
years seperately. is there a way to use the pivot table to calculate
the difference between the 2 years for each customer rather than
calculating the grand total for the 2 years for each customer.
thanks.
T- Hide quoted text -

- Show quoted text -

Thanks. I removed the grand totals for each row and i added a 2nd
field for sum of sales amt. then i changed the data for the 2nd sales
amt field to be displayed as a difference from base yr 2006 like you
said above. I get #N/A as the resulting value. Also, i am not sure
it makes a difference, but i am using Excel '07. Thanks.
T