repost - growth percentage

#1

I am still struggling here
I am not using Custom rollup. All data come from sql table directly
Product Group Ite
ProdA SubA A
ProdA SubA A
ProdA SubA A
...

I tried to create calculated members in measures dimension: "Period-LastYear", "ThisYear - LastYear", "GrowthPerct" (this is (ThisYear - LastYear) / LastYear) and set Solve Order to be 0, 1 and 2, respectively. It didn't help. Is it possible that instead of using the formula that I privided, OLAP is sum up the percentage from the lower level members for GrowthPerct?

----- anonymous (AT) discussions (DOT) microsoft.com wrote: ----

Based on the sample data shown below, are you using a
custom rollup formula for Prod_dim? In that case, Solve
Order of the % fc_var could be relevant - you can try
changing the Solve Order of that measure

Quote:

-----Original Message----
I am stuck... for quite a while
I have a calculated member: forecast_variance in the
measures_dim. It is Forecast of (thisYearthismonth -

lastyearthismonth). I used the ParallelPeriod function. It
works ok

Quote:

However, when I calculate the % of forecast_variance , I
am having a problem. Here is the scenario. In my Prod_dim,

I have ProdA and ProdB. Under ProdA, I have SubA; Under
SubA, I have A1, A2..

Quote:

03/2004 03/2003 fc_var % fc_va
ProdA 0 0 0 (-75%) --> wron
SubA 0 0 0 (-65%) --> wron
A1 10 8 2 ... -->correc
A2 ... ... ... ... --> correc
A3 ... ... ... .. --> correc
A4 0 0 0 NULL --> correc
The problem occures when ProdA for 2003 and 2004 are both
zero. I am getting un-predicable results (the number

changes from time to time) in the % fc column. But the
individual product item is fine. I checked for NULLs and
ZEROs before I do the division. What else can I do

Quote:

I tried growth/lastyearthismonth and (thisYearthismonth -
lastyearthismonth)/lastyearthismonth. Same results.

#2

Could you try to check the individual values of LastYear, This Year, etc so that you could see where it went wrong in your formul

#3

Well, the percent is correct at the lowest level for every single item, i.e. A1, A2, A3 and A4 are calcaulted correctly even when there is no value (NULL) for LastYear or the value was Zero

Another interesting thing is that if I filter the company_dim to division level, every number looked good. I think that is because there are always values (other than 0) for LastYear and (ThisYear - LastYear), for the Highest level, ProdA. The problem is when I am at the company level, these two values are always zero, LastYear is 0 and (thisYear - LastYear) is 0 even though the individual value at the lowest level, A1... may or may not be zero.

Any other thoughts

----- pryly wrote: ----

Could you try to check the individual values of LastYear, This Year, etc so that you could see where it went wrong in your formul

----- anna wrote: ----

I am still struggling here
I am not using Custom rollup. All data come from sql table directly
Product Group Ite
ProdA SubA A
ProdA SubA A
ProdA SubA A
..

I tried to create calculated members in measures dimension: "Period-LastYear", "ThisYear - LastYear", "GrowthPerct" (this is (ThisYear - LastYear) / LastYear) and set Solve Order to be 0, 1 and 2, respectively. It didn't help. Is it possible that instead of using the formula that I privided, OLAP is sum up the percentage from the lower level members for GrowthPerct?

----- anonymous (AT) discussions (DOT) microsoft.com wrote: ----

Based on the sample data shown below, are you using a
custom rollup formula for Prod_dim? In that case, Solve
Order of the % fc_var could be relevant - you can try
changing the Solve Order of that measure

Quote:

-----Original Message----
I am stuck... for quite a while
I have a calculated member: forecast_variance in the
measures_dim. It is Forecast of (thisYearthismonth -

lastyearthismonth). I used the ParallelPeriod function. It
works ok

Quote:

However, when I calculate the % of forecast_variance , I
am having a problem. Here is the scenario. In my Prod_dim,

I have ProdA and ProdB. Under ProdA, I have SubA; Under
SubA, I have A1, A2..

Quote:

03/2004 03/2003 fc_var % fc_va
ProdA 0 0 0 (-75%) --> wron
SubA 0 0 0 (-65%) --> wron
A1 10 8 2 ... -->correc
A2 ... ... ... ... --> correc
A3 ... ... ... .. --> correc
A4 0 0 0 NULL --> correc
The problem occures when ProdA for 2003 and 2004 are both
zero. I am getting un-predicable results (the number

changes from time to time) in the % fc column. But the
individual product item is fine. I checked for NULLs and
ZEROs before I do the division. What else can I do

Quote:

I tried growth/lastyearthismonth and (thisYearthismonth -
lastyearthismonth)/lastyearthismonth. Same results.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

repost - growth percentage

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

repost - growth percentage - 05-19-2004 , 07:01 PM

RE: repost - growth percentage - 05-20-2004 , 11:16 PM

RE: repost - growth percentage - 05-21-2004 , 05:16 PM