Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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