Pick values from different version

[tobtm1]

Hi,

I have a problem. I have created a rule that looks like below. In short, I would say its some kind of "statistical model". It looks for the last two months volumes and then divide that by a value called "denominiator". That value, for example 0,25 is then used to calculate forecast volume for the following months. This works perfectly and I handle shifts in year as well.

The problem though. If the user creates a new version then volume for the "last two months" are off course empty and then it doesnt work. In some way I need to get the volumes without taking the versiondimension into accoutn. Is it possible?

['NA_Volume key'] = N:

IF(ATTRS('Month',!Month,'Prev_Month') @='12',
(DB('S_aggregate','N/A','US_Dummy_Customer',ATTRS('Month',!Month,'Prev_Month'),ATTRS('Year',!Year,'Prev_Year'),'Dummy_plant',!Product aggregate,!Version,'Calculated volume') +
DB('S_aggregate','N/A','US_Dummy_Customer',ATTRS('Month',!Month,'Prev_Prev_Month'),ATTRS('Year',!Year,'Prev_Year'),'Dummy_plant',!Product aggregate,!Version,'Calculated volume')) /
DB('S_aggregate','Dummy_currency',!Customer,!Month,!Year,'Dummy_plant',!Product aggregate,!Version,'NA_Denominator'),

IF(ATTRS('Month',!Month,'Prev_Month') @='01',
(DB('S_aggregate','N/A','US_Dummy_Customer',ATTRS('Month',!Month,'Prev_Month'),!Year,'Dummy_plant',!Product aggregate,!Version,'Calculated volume') +
DB('S_aggregate','N/A','US_Dummy_Customer',ATTRS('Month',!Month,'Prev_Prev_Month'),ATTRS('Year',!Year,'Prev_Year'),'Dummy_plant',!Product aggregate,!Version,'Calculated volume')) /
DB('S_aggregate','Dummy_currency',!Customer,!Month,!Year,'Dummy_plant',!Product aggregate,!Version,'NA_Denominator'),

(DB('S_aggregate','N/A','US_Dummy_Customer',ATTRS('Month',!Month,'Prev_Month'),!Year,'Dummy_plant',!Product aggregate,!Version,'Calculated volume') +
DB('S_aggregate','N/A','US_Dummy_Customer',ATTRS('Month',!Month,'Prev_Prev_Month'),!Year,'Dummy_plant',!Product aggregate,!Version,'Calculated volume')) /
DB('S_aggregate','Dummy_currency',!Customer,!Month,!Year,'Dummy_plant',!Product aggregate,!Version,'NA_Denominator')));


Thanks!

[jim wood]

How do the users create a version? Are you giving them admin rights? If you have some kind of template in place wouldn't it make more sense that they have to input previous volumes so the rule will kick in? After it's not like you're adding new product where you don't have history.

[tomok]

The problem though. If the user creates a new version then volume for the "last two months" are off course empty and then it doesnt work. In some way I need to get the volumes without taking the versiondimension into accoutn. Is it possible?

Where is the volume coming from? Is it actual volume and you are using that to forecast? In that case just hard code the version into your formula:

Code: Select all

['NA_Volume key'] = N:

IF(ATTRS('Month',!Month,'Prev_Month') @='12',
(DB('S_aggregate','N/A','US_Dummy_Customer',ATTRS('Month',!Month,'Prev_Month'),ATTRS('Year',!Year,'Prev_Year'),'Dummy_plant',!Product aggregate,!Version','Calculated volume') +
DB('S_aggregate','N/A','US_Dummy_Customer',ATTRS('Month',!Month,'Prev_Prev_Month'),ATTRS('Year',!Year,'Prev_Year'),'Dummy_plant',!Product aggregate,!Version,'Calculated volume')) /
DB('S_aggregate','Dummy_currency',!Customer,!Month,!Year,'Dummy_plant',!Product aggregate,'Actual','NA_Denominator'),

[tobtm1]

Thanks for your replies... Even if your answers wasnt spot on they made me think and I will probably take the volumes from another cube where version is always the same.

[tobtm1]

...or not. Need to think again...