Pivot tables - calculated fields and items

[Guest]

have a time series of volumes and revenues for a number of landfill sites.
Within a pivot table I have a calculated field working out average price. I
also have in the time dimension calculated items providing an indexation over
time of each field (volume, revenue and price against time zero).
Unfortunately the order of calculation seems predefined so that the
indexation is calculated first and the average price second which invalidates
my indexed average price. Has anyone come across this problem and is there
anything I can do to change this order or workaround this problem?

 

[Roger Govier]

Hi Tim

From the PT toolbar, use the Pivot Table dropdown>Formulas>Solve order

 

[Guest]

Hi Roger

Thanks for your response . Not quite what I was looking for - my fault as my
question is not particularly clear.

What I am trying to do is get my fields calculated before my items. I have
pasetd a simple example of what I am trying to do.

Absolute Index
Volume Revenue Price Volume Revenue Price
Year 1 35 700 20.00 0.0% 0.0% 0.0%
Year 2 33 696 21.09 -5.7% -0.6% 5.5%
Year 3 28 600 21.43 -20.0% -14.3% 7.1%
Year 4 24 601 25.04 -31.4% -14.1% 25.2%
Year 5 18 580 32.22 -48.6% -17.1% 61.1%

I have a time series of volumes and revenue and have defined a calculated
field to give me price. I have then defined items in the time dimension which
calculate indices of movements from Year 1. This works fine for the Volume
and Revenue items but does not work for Price because the pivot calculate the
items first and then the field second. So my Price index is calculated as
Revenue Index/Price Index which is wrong. It should be Revenue/Volume which
is then indexed. So for example the Year 5 Price Index should = 61.1% but the
PT will calculate this = 35.5%.

Sorry this is a bit long winded but hopefully you can see what I am trying
to do.

Any thoughts would be much appreciated.

Kind regards Tim

 

[Roger Govier]

Hi Tim

Without knowing more about your underlying data table, it is difficult
to answer you correctly, but I was wondering why you made the Price a
calculated Item, rather than a calculated Field.
If you can, then remove the calculated item and insert instead a
calculated Field called Price, which is Revenue/Volume.
Then, repeating the 3 fields Volume, Revenue and price in the data area
as % difference from Year 1 data will give you the correct result.

 

[Guest]

Hi Roger

Again I have not explained myself particularly clearly - apologies.

Price is indeed a calculated field in my PT. It is the fact that the items
calculate before the fields that is causing my problem.

Kind regards

Tim

 

[Roger Govier]

Hi Tim

Sorry, I'm still not seeing the problem. Email me directly with a sample
sheet of your data and I will take a look.
Remove NOSPAM from my email address to send.