Cost Index calculation problem

[eliran]

Hi all,

I'm intrested in calculate cost index for products.

The forumla is pretty simple,

X=avg price 2011 * qty 2011

Y=avg price 2010 * qty 2011

Cost Index=(X-Y)/X

My problem is as following, If the quantities are equal to 0 (In 2010 or 2011), I don't want it in my caluclation.

The calculation should be dynamic when changing the selected years, so Set analysis will be used.

Another thing, because it's dynamic I can't think of a flag I can use (In the load script) with the set analysis.

Any help will be welcomed.

Attached is a sample of my problem.

Regards,

Eliran.

[swuehl]

Hi Eliran,

I hope attached comes close to what you want.

Having a dimension of type Date makes it difficult to use Dates in the set expression (because the dimension will not be taken into account in the set expression).

So I created a aggregation table for the avg Prices per product and YearQuarter (only for the periods where the Qty was > 0).

Calculation the cost index is easy then.

This looks ok to me now, please have a look.

Regards,

Stefan