Expecting a logic
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
-
@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
0
Answers
-
What is the logic you need to write? And why can't you add additional line items?
0 -
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
0 -
taget module.rate % = if rate overlay <>0 then rate overlay else previous rate% himself
Is it correct ?
0 -
@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'
0 -
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'
0 -
@rob_marshall my bad .. i misread the requirement .. I thought it was to keep applying the rate - overlay
0 -
All good, I cheated and used both of your ideas…I took the easy way out.
0 -
Thank You everyone for your valuable inputs. It helped.
~Anand
https://in.linkedin.com/in/anand-shekhawat-35645486
0