Complex Pivot Table Function
 

Hello -

I have the below data set.

Product Period Item
A Q1 1
B Q1 2
C Q1 3
D Q1 4
A Q2 4
B Q2 3
C Q2 2
D Q2 1

I'm using a Pivot Table to look up the number of items for each product by
quarter. So I have Product and Period in my rows, and Items is in my data
set.

So the Pivot Table looks like this.

Sum of Item
Product Period Total
A Q1 1
Q2 4
A Total 5
B Q1 2
Q2 3
B Total 5
C Q1 3
Q2 2
C Total 5
D Q1 4
Q2 1
D Total 5
Grand Total 20

I need to add another line only within the pivot table (not the source data)
that looks at the difference between Q2 and Q1.

How do I code this?

Please help, thanks.

Complex Pivot Table Function
 

That is a built-in feature of Pivot Tables. Insert your Item sum into the
data field a second time, then select the second instance of the sum and
right-click it, select "Field Settings" then the "Options" button, then
under "Show data as:" select "Difference from" with the Base Field as Period
and the Base Item as Q1.

HTH,
Bernie
MS Excel MVP


"DyingIsis" wrote in message
...
Hello -

I have the below data set.

Product Period Item
A Q1 1
B Q1 2
C Q1 3
D Q1 4
A Q2 4
B Q2 3
C Q2 2
D Q2 1

I'm using a Pivot Table to look up the number of items for each product by
quarter. So I have Product and Period in my rows, and Items is in my data
set.

So the Pivot Table looks like this.

Sum of Item
Product Period Total
A Q1 1
Q2 4
A Total 5
B Q1 2
Q2 3
B Total 5
C Q1 3
Q2 2
C Total 5
D Q1 4
Q2 1
D Total 5
Grand Total 20

I need to add another line only within the pivot table (not the source
data)
that looks at the difference between Q2 and Q1.

How do I code this?

Please help, thanks.