Take value from another version when value = 0


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?




Data image.PNG



Best Answers

  • ?.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 numbersInput module where you post your numbers



    Output gather and calculate data from inputOutput 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


    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


  • Lukasz,



    I didn't know about NEXTVERSION function.

    I tested and works well.