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?

Hi Tim

From the PT toolbar, use the Pivot Table dropdownFormulasSolve order

--
Regards

Roger Govier


"Tim Wheeler" <Tim wrote in message
...
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?

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" wrote:

Hi Tim

From the PT toolbar, use the Pivot Table dropdownFormulasSolve order

--
Regards

Roger Govier


"Tim Wheeler" <Tim wrote in message
...
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?

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.

--
Regards

Roger Govier


"Tim Wheeler" wrote in message
...
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" wrote:

Hi Tim

From the PT toolbar, use the Pivot Table dropdownFormulasSolve
order

--
Regards

Roger Govier


"Tim Wheeler" <Tim wrote in
message
...
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?

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" wrote:

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.

--
Regards

Roger Govier


"Tim Wheeler" wrote in message
...
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" wrote:

Hi Tim

From the PT toolbar, use the Pivot Table dropdownFormulasSolve
order

--
Regards

Roger Govier


"Tim Wheeler" <Tim wrote in
message
...
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?

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.

--
Regards

Roger Govier


"Tim Wheeler" wrote in message
...
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" wrote:

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.

--
Regards

Roger Govier


"Tim Wheeler" wrote in message
...
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" wrote:

Hi Tim

From the PT toolbar, use the Pivot Table dropdownFormulasSolve
order

--
Regards

Roger Govier


"Tim Wheeler" <Tim wrote in
message
...
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?