Take value from another version when value = 0

yoskaneko

Dear experts,

 

I have 3 versions(Actual, Forecast, Plan).

Then, since forecast have only short term such as 1 or 2 months,

I'd like to copy value from plan data into forecast data, if forecast data is 0.

 

Image of formula:

IF Versions = Forecast and Value=0 THEN Value[Select:Versions.Plan] ELSE Value

 

Could you give any suggestion?

 

Yoshi

 

 

?.Szyma_ski

Hi yoskaneko,

 

I think to solve this riddle you'll need two separate (or one with 'doubled' line items) modules and NEXTVERSION formula. As shown on screens below I've Input & Output modules.

In Output module formulas for items are:

 

Item 2(Value=0) = IF INPUT.'Item 2' = 0 THEN NEXTVERSION(INPUT.'Item 2') ELSE INPUT.'Item 2'

Item 3(Value=0 & Ver.=Forecast) = IF INPUT.'Item 3'[SELECT: VERSIONS.Forecast] = 0 THEN NEXTVERSION(INPUT.'Item 3') ELSE INPUT.'Item 3'

 

Input module where you post your numbers

 

 

Output gather and calculate data from input

Also you may notice that output Item3 has 0 value in Plan version. This is caused by the way formula works there.

It'll constantly refer condition included in IF statement (which will always be true as we use fixed version) and it tries to collect data from next version which does not exist.

I hope that helps.

 

Regards Lukasz.

 

 

Ansar.Sayfin

Hello,

It looks simple:

1) Create new 'Versions' Module' (dimension is versions only) with boolean line item named 'Forecast Version?'. Put the tick manually on the cross with Forecast version (I can suggest formula, but really you need to set this tick one time and that's it);

2) Now create one more line item in existing module (something like Final Value) and the formula would look like: if 'Versions' Module'.'Forcast Version?' and Value=0 then Value[select: versions.plan] else Value

 

Thank you

yoskaneko

Lukasz,

 

Wow.

I didn't know about NEXTVERSION function.

I tested and works well.

Thanks!!

 

Yoshi