Expecting a logic

anand.shekhawat
edited July 2023 in Modeling

 

Hi Anaplanners, The setup is quite self explanatory .

Rate% in the source module is a fixed set of percentages. It could be anything, the numbers shown above is just for the demonstration.

Rate-Overlay in the Target Module is user inputted field. User is supposed to input the overlay numbers here.

The yellow shaded field is the expected final output. I am expecting your help in writing a formula here.

Constraint - You are not allowed to add any additional line item

https://in.linkedin.com/in/anand-shekhawat-35645486

Best Answer

  • alexpavel
    Answer ✓

    @anand.shekhawat, @rob_marshall : It is needed also to check the year-to-date of Rate-Overlay to check if in previous months was anything inserted, otherwise, it will not work if no Rate-Overlay is inserted. Below is the formula for the final "Rate %":

    IF    PREVIOUS(YEARTODATE('Rate-Overlay')) = 0 OR 'Rate-Overlay' <> 0 THEN

    Source Rate % + 'Rate-Overlay'

    ELSE

    PREVIOUS(Rate %)

    Hope it helps

    Alex

Answers

  • @anand.shekhawat

    What is the logic you need to write? And why can't you add additional line items?

  • anand.shekhawat
    edited July 2023

    Hi @rob_marshall

    Thanks for responding.

    I am supposed to write a formula in the 'Rate%' line item in the target module to achieve those numbers.

    So, The source module has a fixed set of 'Rate%' numbers. The 'Rate%' line item in the target module is the one that drives the further calcs in the model. The user is supposed to feed in the overlay numbers. When the overlay numbers are inputted in any of the periods, the Rate%(target module) should take that value = Rate%(source module) + Rate Overlay Value. And that particular value should flow uptill the period-1 of next overlay input. Similar type of calculation needs to be done at every overlay encounter. If there are no overlays, the Rate% numbers from the source needs to be reflected as it is in the target module.

    I hope I am clear with the scenario.

    To answer your second question, I have more then 40 similar line-items which require a similar treatment. That is why I was trying to avoid creating line-items/modules.

    Hope to hear from you.

    Thanks,

    ~Anand

    https://in.linkedin.com/in/anand-shekhawat-35645486

  • taget module.rate % = if rate overlay <>0 then rate overlay else previous rate% himself

    Is it correct ?

  • TristanS
    edited July 2023

    @anand.shekhawat looks like the formula is

    target module.rate% = target module.rate % + if 'Rate - Overlay' = 0 then previous(target module.rate %) - previous(source module.rate %) else 'Rate - Overlay'

  • @axel.frot @TristanS

    I am going to totally cheat and combine both of your answers:

    if 'rate - overlay' <> 0 then previous(Rate %) else source module.rate % + 'rate - overlay'

  • @rob_marshall my bad .. i misread the requirement .. I thought it was to keep applying the rate - overlay

  • @TristanS

    All good, I cheated and used both of your ideas…I took the easy way out.

  • Thank You everyone for your valuable inputs. It helped.

    ~Anand

    https://in.linkedin.com/in/anand-shekhawat-35645486