How to truncate or make this formula more efficient
This formula in our model is possible slowing down the load time of our model. The formula has the logic we need but I believe it can be written better for loading time in our model.
IF 'S04 Version Mapping'.Budget THEN IF 'S01 Time Settings'.Funding Holidays? THEN IF 'S01 Time Settings'.Weekdays? THEN IF WEEKDAY(START()) = 1 THEN OFFSET(SFS Funding Final, -3, 0) ELSE PREVIOUS(SFS Funding Final) ELSE 0 ELSE 'Collections Funding Dayification (Funding)'.Total ELSE IF 'S01 Time Settings'.Actual Date <= 'S02 Time Admin'.Last Actual Day THEN SFS Funding[SELECT: VERSIONS.Actual] ELSE IF 'S01 Time Settings'.Funding Holidays? THEN IF 'S01 Time Settings'.Weekdays? THEN IF WEEKDAY(START()) = 1 THEN OFFSET(SFS Funding Final, -3, 0) ELSE PREVIOUS(SFS Funding Final) ELSE 0 ELSE 'Collections Funding Dayification (Funding)'.Total
Looks like your formatting got messed up a little bit. It would help if you could post a screenshot of how this formula looks in the Anaplan NMX formula editor, as well as a screenshot of your blueprint (so we can see dimensions).
Even without the blueprint, I can see that there are some Planual best practices that are not being followed. Read through the below rules and associated best practice articles as a start:0
Thank you for your response. Attached is a screen shot of both the Blue Print and the Formula.
Thanks for sharing the screenshot of the formula, which helps make the formula clearer. Additionally, thank you for the blueprint, which allows us to see the dimensions involved in the formula.
Without going too far into the logic, the biggest issue I see here is your duplication of expressions (Planual rule: 2.02-03 No Repeated Expressions).
The entire yellow box is repeated twice, and includes repetition inside of it as well. At the very least, the yellow box needs to be its own line item.
Additionally, I see that you've repeated
WEEKDAY(START()) = 1twice in this formula, and used the same logic in multiple other formulas in your blueprint. You need to move this repeated logic to a System module (read: Best practices for module design). For example:
First Day of Week? = WEEKDAY(START()) = 1
You'll also want to switch your IF statements around such that the most common condition is put first (Planual rule: 2.02-17 Put the most common condition first). Knowing the most common condition normally requires that you know your data somewhat well. Of course, you can definitely swap the
First Day of Week?condition as most of the days in a week are not first. e.g.
IF NOT First Day of Week? THEN PREVIOUS(SFS Funding Final) ELSE OFFSET(SFS Funding Final, -3, 0)
In this overall formula, you definitely also want to think about how to reduce nested IFs (Planual rule: 2.02-01).0
- Assign the ' = 1' to a hardcoded system module
- Update the select function to a lookup.
@ramonito While you are correct that normally we want to avoid SELECT, in this case, it is okay since it is on Versions.
See exception 2.02-14a on the Planual rule for 2.02-14 Avoid using SELECT.0
Hi @neg177 ,
can you cut the entire above logic(in blue) and do OR logic with selected/circled in red.
Let me know if that works.
Hi @neg177 ,
Hope you already noticed some performance improvement with above suggestions from @ryan_kohn. To avoid duplciation of same set of logic twice, a slight tweak as below should help. I still recommend to centralise repetitive expressions into a system module, and flip around IF-ELSE logics to keep most common logic fall under IF condition.0
I have a question here, S04 version mapping and S01 time setting are independent, so I believe last SFS Funding[select: versions. Actual] might not process correctly. Because the argument after OR will process for every version.
Correct me, if I am missing anything.
Hi @ManjunathKN ,
My formula argument after OR is the opposite of primary ELSE part in original formula, which should apply for all versions after passing S04 check and actual date check. If you look at the valid cases in original formula for the repetitive logic, it should apply to S04.BUDGET version or if NOT ('S01 Time Settings'.Actual Date <= 'S02 Time Admin'.Last Actual Day).0