Pivot Table Calculated Item-ALL description value

[Dominic]

Hello all,

I am trying to get a calculated item to work correctly. I have a pivot table
with two row fields (code, description), three column fields (plan,
elevation, date) and one data field (amount). I am using Excel 2000 on
Windows 2000.

The row fields have a one-to-one relationship with each other (each code has
one description), therefore each code entry occupies only one line. Each plan
has three elevation values, and each elevation has two date values.

The calculation I am trying to achieve is the difference between the value
(amount) of the first date entry and the second date entry for each cost code.

What I have tried is to click on the date field in the column area,
Formulas:Calculated Item. Then in the calculated item pop-up: click on the
date field, click the most recent date and insert item, type a minus sign,
then click the old date and insert item. Then click OK.

Excel appears to hang at this point. However, I waited long enough (some
fifteen plus minutes) and finally excel came back with a solution. However,
the output was not what I wanted.

The pivot table returned ALL description values for EACH code value in the
row area, with the amount value populated only on the correct (corresponding)
description line. It also appears to have sorted the descriptions WITHIN each
code.

The formula line calculates correctly on the one line per code that has
values.

What did I do wrong?

Example output:
I hope the formatting is legible.

Plan1
Elv1
Code - Desc - Date1 - Date 2 - Formula
1 1D 100 150 50
5D
13D
8D
2D
2 1D
5D
13D
8D
2D 80 120 40


Thanks in advance.

[Dominic]

Well, I came to the conclusion that this is a characteristic/limitation of
Pivot Tables. This behaviour will always occur if this is more than one row
field. It can be solved in this instance because the Code and Description
have a one-to-one relationship. If these values are concatenated in the
original data, then the Pivot will be constructed with one row field, and the
desired output achieved.



"Dominic" wrote:

> Hello all,
>
> I am trying to get a calculated item to work correctly. I have a pivot table
> with two row fields (code, description), three column fields (plan,
> elevation, date) and one data field (amount). I am using Excel 2000 on
> Windows 2000.
>
> The row fields have a one-to-one relationship with each other (each code has
> one description), therefore each code entry occupies only one line. Each plan
> has three elevation values, and each elevation has two date values.
>
> The calculation I am trying to achieve is the difference between the value
> (amount) of the first date entry and the second date entry for each cost code.
>
> What I have tried is to click on the date field in the column area,
> Formulas:Calculated Item. Then in the calculated item pop-up: click on the
> date field, click the most recent date and insert item, type a minus sign,
> then click the old date and insert item. Then click OK.
>
> Excel appears to hang at this point. However, I waited long enough (some
> fifteen plus minutes) and finally excel came back with a solution. However,
> the output was not what I wanted.
>
> The pivot table returned ALL description values for EACH code value in the
> row area, with the amount value populated only on the correct (corresponding)
> description line. It also appears to have sorted the descriptions WITHIN each
> code.
>
> The formula line calculates correctly on the one line per code that has
> values.
>
> What did I do wrong?
>
> Example output:
> I hope the formatting is legible.
>
> Plan1
> Elv1
> Code - Desc - Date1 - Date 2 - Formula
> 1 1D 100 150 50
> 5D
> 13D
> 8D
> 2D
> 2 1D
> 5D
> 13D
> 8D
> 2D 80 120 40
>
>
> Thanks in advance.