Pro tips on troubleshooting large Anaplan formulas
Author: Andrew Barnett is a Certified Master Anaplanner and Director at Allitix.
The Planual serves as our guide for building things the right way in Anaplan. But let's face it, the real world can sometimes be a bit messier. Anaplan environments often consist of a series of models shaped over time by different people with varying levels of experience and training. And we must not forget that as great of a resource as it is, some of us were building in Anaplan for years before the Planual! Because of this, we occasionally run into formulas full of nested IFs, something in direct violation of Planual Rule 2.02-01. Today, we’re going to explore managing this situation in a bit more detail. It's not just about following rules – it's about understanding the history, the growth, and the unique challenges each model faces. Let’s bridge the gap between the rules and the real-life messiness of Anaplan modeling.
Imagine this scenario: It's Monday morning at 8:30 AM. You've already put in a weekend's worth of work and have been awake since 4:30 AM because your team has a crucial presentation to a steering committee member this morning. You've narrowed down the issue to a specific line item, but now you're faced with a daunting task – sifting through a massive formula packed full of nested IFs to identify the root cause of the problem.
How can you figure out which statement contains the problem? See below:
- Step 1: Create a new number formatted line item.
- Step 2: Copy the formula of the line item you’re wishing to check (will work regardless of source format).
- Step 3: Paste the formula in the Anaplan formula editor for the new number formatted line item.
- Step 4: Replace the text after every THEN (not THEN IF), the final ELSE in the formula, and the final ELSE within a THEN IF subsection with a sequential number. I personally do 1 as the first, 2 as the second, etc. Then Submit!
Example original formula:
Example updated formula:
You have a result, great! But be sure to do one more thing, Step 5, first. This is relatively uncommon as it requires multiple nested statements with calculations performed against each other but should always be checked, just in case.
- Step 5: Drill down and ensure that the number shown is not driven by taking multiple values and performing operations with the results. As an example, you could see an output of 10, which was driven by Anaplan taking two statements, one which returned 2 and the second which returned 5 and multiplying them together due to the formula syntax.
Now you can pinpoint the issue! The source line item you were pulling from was missing a SUM on one dimension. Fixed with time for breakfast before the big presentation. Don’t forget to come back and rebuild this line item afterwards!
Known Limitations: There is at least one known limitation to this technique which can be worked around with step 5 above.
In closing, my wish is that you won't find yourself needing to employ this technique often. In a model aligned to best practices, you should never have to. Beyond just ensuring model performance, the significance of following Planual Rule 2.02-01 is apparent for anyone who has ever grappled with unraveling a nested IF statement. However, given that this is an imperfect world, this technique has enabled me and teammates to comb through some very complex formulas while troubleshooting and rebuilding. I believe it can offer you the same assistance during your most challenging moments.
Questions? Leave a comment!
