90% of all the Anaplan modelling you will ever have to design can be accomplished with SUM, LOOKUP and IF THEN ELSE - what about the others?

If 90% of all the Anaplan modelling you will ever have to design can be accomplished with SUM, LOOKUP and IF THEN ELSE then what exotic beauties are possible if you max out using the other 10%?

We have discovered some great uses for those rarer functions that perhaps are less well known. Here are our favourite;

▫ RANK

Beyond the obvious uses to rank any given values across an collection of dimensions we have found that RANK comes in handy when we need to create a unique sequence over a collection of identical outcomes. For example, when we need to generate codes to use in list building when we have identical names RANK allows us to create an index number which we can uses to create a unique code.

▫ ANY

We have found that ANY is very useful in querying a combination list and determining whether a list item from one of the lists used in creating the combination list is in use. For example, when we import data into a list using combination of properties we are effectively creating a combination list using the list properties. Once the properties are mapped to their respective lists we can then use ANY to determine whether list items are is use. If we choose to we can then delete them.

▫ FIND

We use FIND to determine the starting position when extracting individual list codes from a combination list code. For example, we have the following combination list code, AAA_BBB_CCC with each three letter code referring to a list item from three individual lists. Using FIND we can search the combination list item code to locate the "_" and then use MID function to extract the individual code. This allows us 'hard' code the combination mapping rather than relying on importing the mapping references when building the combination list.

▫ ISANCESTOR

We design more dynamic user filters when we combine a ragged hierarchy with ISANCESTOR function. For example, when a user selects any summary level in the filter selector we can use ISANCESTOR to asses whether the selection is an upstream relation of the downstream lists items. This is more dynamic than relying on multiple selections that would be required when using separate composite or balanced hierarchies as the user only has to make one selection to view all related downstream list relations.

▫ PREVIOUS & NEXT

These two functions allow us to design a calculation logic which relies on values in the PREVIOUS or NEXT time period. We use these functions when creating a cumulative total such as in balance sheet or cash flow modelling.

▫ POST, LAG and LEAD

When we need to design calculation logic which moves data between time periods we will use POST, LAG and LEAD. These functions can also be used to avoid circular references when in STRICT mode which is a very powerful feature.