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.
Related to Rule:2.02-05 Create "joins" in smallest hierarchy2.03-02 Try not to use TEXT as a format, or limit it as much as possible
Best Practices articles:Formula Optimisation in Anaplan
Rule 2.02-04 Text StringsTreat text strings with caution. Try and avoid multiple joins and split common joins to separate line items. It goes against PERFORMANCE, LOGICAL, NECESSARY elements of PLANS if you wish to go against this rule
Here is how it was done in Pre Planual Era:
Let’s assume we have a module that is dimensioned by Company, Products, and Months. To allow us to connect to another part of the model, we need to create a combined text string that is the code of the Company and the code of the Product. We could create a text formatted line item with the following formula:
This will give the following result:
What is wrong with this method?
The same result is calculated for ALL Products and ALL months even though the expression does not relate to those dimensions.
The same happens for the second expression, CODE(ITEM(Product)).
The same result is returned for ALL Companies for ALL months.
And finally, let’s see what the “_“ is doing:
The text is “calculating” for ALL Companies, for ALL Products, for ALL months.
This is a classic case of Over Calculating. We do not need to calculate any of these expressions by month, and each expression only needs to calculate for the applicable list, or dimension. This is what I meant by “appropriate dimensionality"; we should only calculate expressions with dimensions that are appropriate to affect the result.
There is a simple rule to apply.
If the dimensions of the line item are greater than the appropriate dimensions for the expression, then the expression should be done elsewhere.
Here is how it should be done in Planual Way:
CODE() expression should be done in a module that only contains Company (e.g. Company Details), and the second CODE() expression, in a module only dimensioned by Product (Product Details).
Following the DISCO methodology, you should have System modules for the Company and Products lists, so this is the natural place to house the new line items.
We can then bring them together in the target module.
This will give significant savings in calculation time.
Note: There is a simple rule to apply.