Expecting a logic

anand.shekhawat

 

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

alexpavel

@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

rob_marshall

@anand.shekhawat

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

anand.shekhawat

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

axel.frot

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

Is it correct ?

TristanS

@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'

rob_marshall

@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'

TristanS

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

rob_marshall

@TristanS

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

axel.frot

@rob_marshall @TristanS

👍️

anand.shekhawat

Thank You everyone for your valuable inputs. It helped.

~Anand