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 Text strings
Treat text strings with caution. Try and avoid multiple joins and split common joins to separate line items. Make use of IF ISBLANK() when joining text. If the strings are empty, set to BLANK.
relatedToRule
2.02-05 Create "joins" in smallest hierarchy
If a text string join is needed, create the joins in the smaller lists first to minimize the size of the text strings.
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 Never use SUM and LOOKUP together
SUM and LOOKUP used in the same expression generally cause large formula calculations and may cause intermediate relationship calculations, especially if Time is a dimension or when the source and target structures are very different. Splitting them into different modules and line items considerably reduces the size of the intermediate calculations.
exception
| 2.02-08a The LOOKUP is a constant | Where the LOOKUP line item is a constant, the formula will operate as a SELECT, so the performance implications are negated. |
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 Using PREVIOUS() vs CUMULATE()
For long timescales, using PREVIOUS is faster than CUMULATE due to the number of "reads" required for the calculation. So, the expression should be: 'Calc line item' = 'data line item' + PREVIOUS('Calc line item') rather than CUMULATE('data line item').
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 FINDITEM on blanks
Doing a FINDITEM() on blank values is inefficient as the function has to traverse the entire list before it returns a blank.
If the majority are NOT blank:
- If ISNOTBLANK(Line Item) THEN FINDITEM(List, line item) ELSE BLANK
If the majority are blank:
- If ISBLANK(Line Item) THEN BLANK else FINDITEM(List, line item)
The number of blanks the line item could have will dictate which formula to use.. If there are more blanks than not, then use the second formula. If Line Item is more dense (has more values than not), then the first formula should be used. If there will never be blanks in the values, there's no need to check for blanks:
- FINDITEM(List, line item)
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].
