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.