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 Evaluate use of repeated expressions carefully
In Classic Hyperblock, avoid repeated expressions by splitting them into separate line items to ensure they are calculated only once. Engine Applicability: Classic Hyperblock and Polaris |
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 Compare numbers using logical operators rather than math when checking for zero
| It is always faster to check A = B rather than A - B = 0. Please note that this optimization specifically applies to comparing number-formatted line items. Engine Applicability: Classic Hyperblock and Polaris |
2.02-07 Use Booleans instead of 1s & 0s
| Booleans take up 1/8th of the space of a number. Unless a numeric value is explicitly required for a calculation, try to use TRUE or FALSE instead of 1s and 0s. Engine Applicability: Classic Hyperblock Only |
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 Utilize aggregation rules (ANY, ALL, FIRSTNONBLANK, LASTNONBLANK)
| Utilise these summary methods to minimise the use of additional line items and IF statements. Engine Applicability: Classic Hyperblock Only |
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 Do not hardcode references to List members
| Avoid making direct references to specific list items in formulas (for example, IF ITEM(list)=list.xx. Instead, create a system SYS module with a Boolean-formatted line item to drive logic. This best practice makes formulas more dynamic, allows multiple members to leverage the same logic without requiring manual formula updates, and improves overall model maintainability. Engine Applicability: Classic Hyperblock and Polaris |
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 hardcoding values with the SELECT function whenever possible. Instead, use a constants module and the LOOKUP function to reference them. Engine Applicability: Classic Hyperblock |
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 in an IF/THEN formula
| When writing a formula with an IF/THEN statement, put the "most true" conditional first in your formula. This enables "Early Exit" behavior: The engine will stop evaluating the formula once the first true condition is reached, thus ending calculation as early as possible will reduce calculation effort. Engine Applicability: Classic Hyperblock |
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 Avoid Using TIMESUM with Time
| Don't use TIMESUM formulas in a line item that applies to Time, as the engine will unnecessarily duplicate the calculation for each time period. Alternative Solutions: For rolling or cumulative totals: Use MOVINGSUM or YEARTODATE. For the entire timescale total: Use Line Item[Select: Time. All Periods].Engine Applicability: Classic Hyperblock and Polaris |
