software

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 dropdowngt;Formulasgt;Solve order

--
Regards

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

AbsoluteIndex
VolumeRevenuePriceVolumeRevenuePrice
Year 13570020.000.0%0.0%0.0%
Year 23369621.09-5.7%-0.6%5.5%
Year 32860021.43-20.0%-14.3%7.1%
Year 42460125.04-31.4%-14.1%.2%
Year 51858032.22-48.6%-17.1a.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

quot;Roger Govierquot; wrote:

gt; Hi Tim
gt;
gt; From the PT toolbar, use the Pivot Table dropdowngt;Formulasgt;Solve order
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;Tim Wheelerquot; lt;Tim gt; wrote in message
gt; ...
gt; gt; have a time series of volumes and revenues for a number of landfill
gt; gt; sites.
gt; gt; Within a pivot table I have a calculated field working out average
gt; gt; price. I
gt; gt; also have in the time dimension calculated items providing an
gt; gt; indexation over
gt; gt; time of each field (volume, revenue and price against time zero).
gt; gt; Unfortunately the order of calculation seems predefined so that the
gt; gt; indexation is calculated first and the average price second which
gt; gt; invalidates
gt; gt; my indexed average price. Has anyone come across this problem and is
gt; gt; there
gt; gt; anything I can do to change this order or workaround this problem?
gt; gt;
gt;
gt;
gt;

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 Govierquot;Tim Wheelerquot; gt; wrote in message
...
gt; Hi Roger
gt;
gt; Thanks for your response . Not quite what I was looking for - my fault
gt; as my
gt; question is not particularly clear.
gt;
gt; What I am trying to do is get my fields calculated before my items. I
gt; have
gt; pasetd a simple example of what I am trying to do.
gt;
gt; Absolute Index
gt; Volume Revenue Price Volume Revenue Price
gt; Year 1 35 700 20.00 0.0% 0.0% 0.0%
gt; Year 2 33 696 21.09 -5.7% -0.6% 5.5%
gt; Year 3 28 600 21.43 -20.0% -14.3% 7.1%
gt; Year 4 24 601 25.04 -31.4% -14.1% 25.2%
gt; Year 5 18 580 32.22 -48.6% -17.1% 61.1%
gt;
gt; I have a time series of volumes and revenue and have defined a
gt; calculated
gt; field to give me price. I have then defined items in the time
gt; dimension which
gt; calculate indices of movements from Year 1. This works fine for the
gt; Volume
gt; and Revenue items but does not work for Price because the pivot
gt; calculate the
gt; items first and then the field second. So my Price index is calculated
gt; as
gt; Revenue Index/Price Index which is wrong. It should be Revenue/Volume
gt; which
gt; is then indexed. So for example the Year 5 Price Index should = 61.1%
gt; but the
gt; PT will calculate this = 35.5%.
gt;
gt; Sorry this is a bit long winded but hopefully you can see what I am
gt; trying
gt; to do.
gt;
gt; Any thoughts would be much appreciated.
gt;
gt; Kind regards Tim
gt;
gt; quot;Roger Govierquot; wrote:
gt;
gt;gt; Hi Tim
gt;gt;
gt;gt; From the PT toolbar, use the Pivot Table dropdowngt;Formulasgt;Solve
gt;gt; order
gt;gt;
gt;gt; --
gt;gt; Regards
gt;gt;
gt;gt; Roger Govier
gt;gt;
gt;gt;
gt;gt; quot;Tim Wheelerquot; lt;Tim gt; wrote in
gt;gt; message
gt;gt; ...
gt;gt; gt; have a time series of volumes and revenues for a number of landfill
gt;gt; gt; sites.
gt;gt; gt; Within a pivot table I have a calculated field working out average
gt;gt; gt; price. I
gt;gt; gt; also have in the time dimension calculated items providing an
gt;gt; gt; indexation over
gt;gt; gt; time of each field (volume, revenue and price against time zero).
gt;gt; gt; Unfortunately the order of calculation seems predefined so that the
gt;gt; gt; indexation is calculated first and the average price second which
gt;gt; gt; invalidates
gt;gt; gt; my indexed average price. Has anyone come across this problem and
gt;gt; gt; is
gt;gt; gt; there
gt;gt; gt; anything I can do to change this order or workaround this problem?
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
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

quot;Roger Govierquot; wrote:

gt; Hi Tim
gt;
gt; Without knowing more about your underlying data table, it is difficult
gt; to answer you correctly, but I was wondering why you made the Price a
gt; calculated Item, rather than a calculated Field.
gt; If you can, then remove the calculated item and insert instead a
gt; calculated Field called Price, which is Revenue/Volume.
gt; Then, repeating the 3 fields Volume, Revenue and price in the data area
gt; as % difference from Year 1 data will give you the correct result.
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;Tim Wheelerquot; gt; wrote in message
gt; ...
gt; gt; Hi Roger
gt; gt;
gt; gt; Thanks for your response . Not quite what I was looking for - my fault
gt; gt; as my
gt; gt; question is not particularly clear.
gt; gt;
gt; gt; What I am trying to do is get my fields calculated before my items. I
gt; gt; have
gt; gt; pasetd a simple example of what I am trying to do.
gt; gt;
gt; gt; Absolute Index
gt; gt; Volume Revenue Price Volume Revenue Price
gt; gt; Year 1 35 700 20.00 0.0% 0.0% 0.0%
gt; gt; Year 2 33 696 21.09 -5.7% -0.6% 5.5%
gt; gt; Year 3 28 600 21.43 -20.0% -14.3% 7.1%
gt; gt; Year 4 24 601 25.04 -31.4% -14.1% 25.2%
gt; gt; Year 5 18 580 32.22 -48.6% -17.1% 61.1%
gt; gt;
gt; gt; I have a time series of volumes and revenue and have defined a
gt; gt; calculated
gt; gt; field to give me price. I have then defined items in the time
gt; gt; dimension which
gt; gt; calculate indices of movements from Year 1. This works fine for the
gt; gt; Volume
gt; gt; and Revenue items but does not work for Price because the pivot
gt; gt; calculate the
gt; gt; items first and then the field second. So my Price index is calculated
gt; gt; as
gt; gt; Revenue Index/Price Index which is wrong. It should be Revenue/Volume
gt; gt; which
gt; gt; is then indexed. So for example the Year 5 Price Index should = 61.1%
gt; gt; but the
gt; gt; PT will calculate this = 35.5%.
gt; gt;
gt; gt; Sorry this is a bit long winded but hopefully you can see what I am
gt; gt; trying
gt; gt; to do.
gt; gt;
gt; gt; Any thoughts would be much appreciated.
gt; gt;
gt; gt; Kind regards Tim
gt; gt;
gt; gt; quot;Roger Govierquot; wrote:
gt; gt;
gt; gt;gt; Hi Tim
gt; gt;gt;
gt; gt;gt; From the PT toolbar, use the Pivot Table dropdowngt;Formulasgt;Solve
gt; gt;gt; order
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Regards
gt; gt;gt;
gt; gt;gt; Roger Govier
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Tim Wheelerquot; lt;Tim gt; wrote in
gt; gt;gt; message
gt; gt;gt; ...
gt; gt;gt; gt; have a time series of volumes and revenues for a number of landfill
gt; gt;gt; gt; sites.
gt; gt;gt; gt; Within a pivot table I have a calculated field working out average
gt; gt;gt; gt; price. I
gt; gt;gt; gt; also have in the time dimension calculated items providing an
gt; gt;gt; gt; indexation over
gt; gt;gt; gt; time of each field (volume, revenue and price against time zero).
gt; gt;gt; gt; Unfortunately the order of calculation seems predefined so that the
gt; gt;gt; gt; indexation is calculated first and the average price second which
gt; gt;gt; gt; invalidates
gt; gt;gt; gt; my indexed average price. Has anyone come across this problem and
gt; gt;gt; gt; is
gt; gt;gt; gt; there
gt; gt;gt; gt; anything I can do to change this order or workaround this problem?
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

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 Govierquot;Tim Wheelerquot; gt; wrote in message
...
gt; Hi Roger
gt;
gt; Again I have not explained myself particularly clearly - apologies.
gt;
gt; Price is indeed a calculated field in my PT. It is the fact that the
gt; items
gt; calculate before the fields that is causing my problem.
gt;
gt; Kind regards
gt;
gt; Tim
gt;
gt; quot;Roger Govierquot; wrote:
gt;
gt;gt; Hi Tim
gt;gt;
gt;gt; Without knowing more about your underlying data table, it is
gt;gt; difficult
gt;gt; to answer you correctly, but I was wondering why you made the Price a
gt;gt; calculated Item, rather than a calculated Field.
gt;gt; If you can, then remove the calculated item and insert instead a
gt;gt; calculated Field called Price, which is Revenue/Volume.
gt;gt; Then, repeating the 3 fields Volume, Revenue and price in the data
gt;gt; area
gt;gt; as % difference from Year 1 data will give you the correct result.
gt;gt;
gt;gt; --
gt;gt; Regards
gt;gt;
gt;gt; Roger Govier
gt;gt;
gt;gt;
gt;gt; quot;Tim Wheelerquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hi Roger
gt;gt; gt;
gt;gt; gt; Thanks for your response . Not quite what I was looking for - my
gt;gt; gt; fault
gt;gt; gt; as my
gt;gt; gt; question is not particularly clear.
gt;gt; gt;
gt;gt; gt; What I am trying to do is get my fields calculated before my items.
gt;gt; gt; I
gt;gt; gt; have
gt;gt; gt; pasetd a simple example of what I am trying to do.
gt;gt; gt;
gt;gt; gt; Absolute Index
gt;gt; gt; Volume Revenue Price Volume Revenue Price
gt;gt; gt; Year 1 35 700 20.00 0.0% 0.0% 0.0%
gt;gt; gt; Year 2 33 696 21.09 -5.7% -0.6% 5.5%
gt;gt; gt; Year 3 28 600 21.43 -20.0% -14.3% 7.1%
gt;gt; gt; Year 4 24 601 25.04 -31.4% -14.1% 25.2%
gt;gt; gt; Year 5 18 580 32.22 -48.6% -17.1% 61.1%
gt;gt; gt;
gt;gt; gt; I have a time series of volumes and revenue and have defined a
gt;gt; gt; calculated
gt;gt; gt; field to give me price. I have then defined items in the time
gt;gt; gt; dimension which
gt;gt; gt; calculate indices of movements from Year 1. This works fine for the
gt;gt; gt; Volume
gt;gt; gt; and Revenue items but does not work for Price because the pivot
gt;gt; gt; calculate the
gt;gt; gt; items first and then the field second. So my Price index is
gt;gt; gt; calculated
gt;gt; gt; as
gt;gt; gt; Revenue Index/Price Index which is wrong. It should be
gt;gt; gt; Revenue/Volume
gt;gt; gt; which
gt;gt; gt; is then indexed. So for example the Year 5 Price Index should =
gt;gt; gt; 61.1%
gt;gt; gt; but the
gt;gt; gt; PT will calculate this = 35.5%.
gt;gt; gt;
gt;gt; gt; Sorry this is a bit long winded but hopefully you can see what I am
gt;gt; gt; trying
gt;gt; gt; to do.
gt;gt; gt;
gt;gt; gt; Any thoughts would be much appreciated.
gt;gt; gt;
gt;gt; gt; Kind regards Tim
gt;gt; gt;
gt;gt; gt; quot;Roger Govierquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi Tim
gt;gt; gt;gt;
gt;gt; gt;gt; From the PT toolbar, use the Pivot Table dropdowngt;Formulasgt;Solve
gt;gt; gt;gt; order
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; Regards
gt;gt; gt;gt;
gt;gt; gt;gt; Roger Govier
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Tim Wheelerquot; lt;Tim gt; wrote in
gt;gt; gt;gt; message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; have a time series of volumes and revenues for a number of
gt;gt; gt;gt; gt; landfill
gt;gt; gt;gt; gt; sites.
gt;gt; gt;gt; gt; Within a pivot table I have a calculated field working out
gt;gt; gt;gt; gt; average
gt;gt; gt;gt; gt; price. I
gt;gt; gt;gt; gt; also have in the time dimension calculated items providing an
gt;gt; gt;gt; gt; indexation over
gt;gt; gt;gt; gt; time of each field (volume, revenue and price against time
gt;gt; gt;gt; gt; zero).
gt;gt; gt;gt; gt; Unfortunately the order of calculation seems predefined so that
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; indexation is calculated first and the average price second
gt;gt; gt;gt; gt; which
gt;gt; gt;gt; gt; invalidates
gt;gt; gt;gt; gt; my indexed average price. Has anyone come across this problem
gt;gt; gt;gt; gt; and
gt;gt; gt;gt; gt; is
gt;gt; gt;gt; gt; there
gt;gt; gt;gt; gt; anything I can do to change this order or workaround this
gt;gt; gt;gt; gt; problem?
gt;gt; gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;