Planual rules regarding Formulas.
2.02-01 Nested IFs
Avoid using multiple IFs. It's better to split the formula into several line items and use LOOKUPs or alternative constructs.
bestPracticesArticles
2.02-02 < 12 expressions in a formula
If it takes you longer than one simple sentence to describe what the formula is doing, it's probably too long. Try not to mix expressions.
2.02-03 No repeated expressions
If the expression is repeated in the formula (or other modules), put it on a separate line item. “Calculate once, reference many times.”
2.02-04 Concatenate text strings with caution
Avoid multiple concatenations. Split commonly used concatenations into separate line items.
Make use of IF ISBLANK() when concatenating text with delimiters and set to BLANK to avoid unnecessarily populating cells . Because the text data type consumes more memory than other formats (Classic only) minimizing their use and avoiding unnecessary calculations is crucial for optimal model performance.
Engine Applicability: Classic Hyperblock and Polaris
relatedToRule
2.02-05 Create "joins" in smallest hierarchy
| If a text string concatenation is needed, build it in the smallest possible dimensionality. This minimizes the size of the text strings and significantly reduces calculation effort and memory usage. Guards should be used in Polaris to reduce populated cell count. Engine Applicability: Classic Hyperblock and Polaris |
relatedToRule
bestPracticesArticles
2.02-06 Comparing numbers
It's faster to check A=B rather than A-B=0.
2.02-07 Use Booleans instead of 1s & 0s
In Classic, booleans take up 1/8th of the space as a number, so unless a numeric value is needed try to use TRUE or FALSE.
2.02-08 Avoid combining SUM and LOOKUP
Never combine SUM and LOOKUP within the same formula expression. This combination forces the calculation engine to perform a massive number of calculations and creates slow intermediate relationship mappings, especially when Time is a dimension or when source and target structures differ. To drastically reduce calculation size and optimize performance, you must always split SUM and LOOKUP operations into separate modules and line items.
Engine Applicability: Classic Hyperblock and Polaris
bestPracticesArticles
2.02-09 Aggregation rules (ANY, ALL, FIRSTNONBLANK, LASTNONBLANK)
Use these summary methods to minimize the use of additional line items and IF statements.
2.02-10 Use PREVIOUS rather than CUMULATE
| For long timescales or high cell counts, using the PREVIOUS() function is faster than the CUMULATE() function due to the potential number of cells required for the calculation. Use the expression: 'Calc line item' = 'data line item' + PREVIOUS('Calc line item') rather than CUMULATE('data line item'). Engine Applicability: Classic Hyperblock and Polaris |
exception
| 2.02-10a Short timescales | Where the number of periods is small (Year granularity with a small number of years), CUMULATE is faster. |
bestPracticesArticles
2.02-11 Avoid using TEXTLIST()
TEXTLIST() requires a lot of memory for calculations and should be avoided if possible. Using two dimensional modules and Boolean flags with ANY is a better alternative. Other alternatives are using FIRSTNONBLANK as well as LASTNONBLANK.
2.02-12 No Hardcoding to List members
Avoid direct references to list item, for example, IF ITEM(list)=list.xx. Instead, use a SYS module with a line item having a Boolean format as this makes the formula more dynamic (multiple members can use the same logic).
bestPracticesArticles
2.02-13 Only use POST for its specific purpose
Don't use POST for simple data offsets as OFFSET, LAG, or MOVINGSUM are more efficient.
2.02-14 Avoid using SELECT
Avoid hard coding using SELECT if possible. Use a constants module and LOOKUP instead.
exception
| 2.02-14a Versions | It's OK to use SELECT for versions. |
| 2.02-14b For top-level items on lists | It's OK to reference the Top Level item of a list with SELECT. For other items (actual list members), it's better to use mock parent lists and additional modules. |
relatedToRule
bestPracticesArticles
2.02-15 Avoid using FINDITEM on blank values
Performing a FINDITEM() on blank values creates inefficiency, as the engine must traverse the entire list before returning a blank. To optimize calculation performance, evaluate the data's density and use conditional logic to bypass blanks. If the data is mostly blank check using ISBLANK: IF ISBLANK(Line Item) THEN BLANK ELSE FINDITEM(List, Line Item) If the data is mostly populated check using ISNOTBLANK: IF ISNOTBLANK(Line Item) THEN FINDITEM(List, Line Item) ELSE BLANK By putting the most common condition first, the engine works faster. If the source line item will never contain blanks: you can omit the conditional check entirely and simply use FINDITEM(). |
relatedToRule
2.02-16 Use conditionals to stop unnecessary calculations
In multiple conditional statements, try and include a conditional to prevent further referencing in the formula if the condition is satisfied.
2.02-17 Put the most common condition first
For formula efficiency, put the conditional with the most common occurrence first in the formula.
2.02-18 Break up formulas
The engine works more efficiently when calculations are broken up into separate line items. So, break up formula expressions where possible. This is especially true for calculations that are referenced many times and/or calculations that don't change often.
bestPracticesArticles
2.02-19 Don't daisy-chain data
Always refer back to the ultimate source if possible, to avoid creating more dependencies than necessary. This allows more parallel calculations to be run, increasing efficiency and speeding up calculations.
bestPracticesArticles
2.02-20 Don't use RANK formulas with large lists
RANK is a calculation-intensive formula that can't multi-thread. Used in conjunction with large lists, it can lead to poorly performing calculations. The same applies to RANKCUMULATE and ISFIRSTOCCURRENCE.
2.02-21 Don't use TIMESUM with Time
TIMESUM formulas shouldn't be used in a line item that applies to Time as the calculation will be duplicated for each time period.
If the line item contains time periods, consider using MOVINGSUM or YEARTODATE. If you need the total for the entire timescale, consider using line item[Select: Time. All Periods].
