Nested Ifs  Model Optimization Team series
Hello! Welcome to the Model Optimization team's monthly blog post. This is an eightpart series, following the topics we see occurring most frequently in slow customer models. Our aim is to inform and guide how to build with best practice intentions for these model building topics.
Each month, two of our analysts will take you through how to spot and improve these formula structures! We hope these can help with your model performance.
All the best  Cianna Bramley (Model Optimization Manager)
……
This month's blog is brought to you by Rosa Eliza Dimaano and Mai Hashad.
This month, we'll look at how the formula "Nested Ifs" influences your model's performance. To ensure the optimal model performance, it is essential to consider the implications of modeling decisions to prevent the computation engine from being overworked.
As discussed in P.L.A.N.S. — the standard of Anaplan modeling — formulas should be built to be logical and performant. Optimizing Nested IF formulas will help work towards these recommendations.
This article provides some suggestions for reducing the amount of computation required on the calculating engine using Nested IFs formula. Simple examples are provided to show the varied solutions to different types of Nested IF structures.
When is this a problem?
An excellent way to identify a problematic nested IF THEN ELSE statement is by finding a clear objective of the formula.
If you can't explain the purpose of a formula in one sentence, the formula is too long.
Why is this bad?
Referencing Planual 2.0201 Nested IFs, avoid using multiple IFs. Long formulas, such as complex IF THEN ELSE statements are difficult to understand and maintain. Complex and long calculations consume significant processing time and can cause the engine to work harder than necessary.
Depending on the structure, it is better to split the formula into more line items, or alternative constructs like LISS.
How to find it?
There are three ways to find Nested IFs in your model:
 Manual Inspection
 Search the blueprint of the module and look for a line item with multiple IF THEN ELSE formulas.
 Download the line items and search in the csv file.
 Go to Modules and click “Export”.
 Click "Run Export".
 MAPs: this is a free report your BP can provide up to twice per month. It shows Best Practice checks which includes highlighting Nested IF occurrences.
 Request Model Optimization reports through Hypercare Analysts or BPs, which analyze the models for best practices and Planual violations including NESTED IFs, as well as providing specific suggestions on how to improve slow formulas.
How do we fix it?
To fix this issue, we suggest the following steps:
 For long formulas, decrease the length of the formula by splitting it into multiple line items.
 Use a LOOKUP.
 Use line item subsets (LISS) where the ‘checks’ provide different outputs for each item in a list dimension.
 Combine ‘checks’ which provide the same outputs.
Examples
Example 1: Multiple Nested IFs that can be split into another line item
This is a simple example of a common occurrence we see; a large number of IFs with varied outputs. To optimize this line item we can create an intermediate line item.
Step 1: Create intermediate line item:
Setting the Summary to “NONE” to minimize the number of cell count.
Step 2: Reference the intermediate line item to original line item:
Example 2: Using LOOKUP
This example demonstrates using a hardcoded text that increases the calculation duration of the formula. The line item can be optimized by using LOOKUP and by eliminating the hardcoded text.
Step 1: Create a mapping module containing the converted Kilogram data:
Step 2: Manually input the data:
Step 3: Create a new line item using FINDITEM.
Syntax
: FINDITEM(List_or_Time, Item_to_find)
The FINDITEM function dimensioned based on the “Item to find”. For this case, we will only use “Size” because the item “Sugar(gram)” applies only to "Size".
Note: Make use of IF ISBLANK(Item to find)THEN BLANK if you know there will be occurrences when the source data is empty; this avoids calculating the FINDITEM unless necessary.
Step 4: Finally, reference the module created in step 1 and the line item created in step 3. The formula becomes:
Note: If necessary, you can set the summary to "LASTNONBLANK" or "FIRSTNONBLANK".
The simpler the better, because it is easier to understand and to main instead of using long/complex formulas of IF THEN ELSE.
You can get more details about this from the community article Decreasing the Length of Your Formulas.
Example 3: Using Line Item Subset
Formula:
Step 1: Create Intermediate line Items for the SELECTs
Step 2: Create a LISS (Line Item Subset)
Use the intermediate line item created in step 1 to create a LISS.
Refer to this Community Article for more information on Line Item Subsets. Please be advised of Planual Rule 1.0701 which refers to the LISS naming convention.
Line item subsets can use multiple line items and turns these line items into a list that can be used for calculations, but certain limitations apply:
 Line item subsets can only contain numeric formatted line items.
 Only one line item subset can be used as a dimension in a module.
 Although line items can contain formulas, the items in a line item subset can only aggregate to a simple subtotal.
 Styles on the line items are not transferred over to the line item subset.
See the article Line item subsets demystified for more information.
Step 3: To extract the information from the LISS, create a Collection module.
To create the formula, type COLLECT() and set of the Summary to None. The dimensions of this line item should match the OPE02 Version Analysis module, with the LISS added as an additional dimension.
Step 4: Create a mapping line item.
Step 5: Apply the new created line items to the old formula.
To create the Formula, use 'COL01 Versions'.Collect[LOOKUP: Version Label]
Example 4: Combining similar outputs
Here we have a basic example showing two outputs which are identical. These can be merged:
You could also create boolean line items in the Systems module for the ‘country’ list (eg. SYS Country.EMEA Countries) and tick any relevant items for each section. This would help reduce multiple checks on countries for each output.
Summary
Simple is better! Extended Nested IF THEN ELSE statements can negatively impact the performance because it turns the formula into a singlethreaded calculation. Keep in mind that it is preferable to split the formulas into several line items or use a LISS rather than compressing all of them into a single large line item.
Questions? Leave a comment!
…..
Check out the other blogs in the series:
Comments

@modeloptimizationteam Great share as usual. Would request to share the next 5 articles as soon as possible, 5 months is too long and all these suggestions needs to be applied from Day 1
Please start posting these blogs on fortnight basis
V.Sai Bharadwaj
0