5 tips & tricks: An Anaplan model builder’s favorite formulas
Are you an Anaplanner looking to hone your skills? With the holidays around the corner, I would like to give you the gift of my five favorite formulas for model building. Explore the formulas below and happy modeling from all of us at Anaplan.
There are two forms of TEXTLIST, and this one covers the aggregation function. The TEXTLIST function lets you combine the individual text cells into a comma-separated list: E.g. Shorts + Pants + Shirts = Shorts, Pants, Shirts. The mapping determines which items in the source are combined into each cell of the result. Both the source and the result must be of data type ‘text.’ I like using this formula when I have transactional data. It allows you to pull back information based on mapping. You can even add TEXTLIST to a formula that includes a SELECT or a LOOKUP.
Formula Syntax – Source[TEXTLIST: Mapping]
This is more than just a sum! Though the formula defaults to SUM, you can use all of the aggregate functions in Anaplan including, AVERAGE, MIN, MAX, ANY, ALL, FIRST_NON_BLANK, LAST_NON_BLANK, TEXTLIST. This formula requires the time dimension because the values are always relative to the period that contains the result. Looking to calculate a rolling four-quarter average? Want to track employee title changes over the months? This formula allows you to do that and more.
Formula Syntax – MOVINGSUM(Item,From,To,Aggregate)
New to the Anaplan platform in our 2015.4 release, this formula allows you to not only rank values in a list, but to cumulate them in a running aggregate as well. You can also include groupings to partition the rankings. I find myself using this formula in sales commission use cases to calculate total-to-date components.
In the example below, you can see the transactions attached to a sales rep and the RANKCUMULATE formula being used to aggregate the transactions by sales rep (Group) and based on the date sold (Rank Source). Once cumulated, you can compare against your goal and assess your payout rate, Base vs. Overbase.
Formula Syntax – RANKCUMULATE(CumulationSource,Rank Source,Direction,Include,Group)
This formula finds a valid item in a list or time period list by matching a text string to the list members. It takes a list as the first parameter and text as the second parameter and matches the text to a list item member. You can use FINDITEM to search a list and return a matched list member, if it belongs to the list.The key to this formula is to make sure that you assign codes to your lists, especially for numbered lists.
In the module below, the transactional information has been loaded to a list, and now the information needs to be dimensionalized. One of the attributes we have loaded is Employee ID. With the Employee ID as text, we can use FINDITEM to match the appropriate sales rep. With that information, you can summarize the information by the Sales Rep dimension.
Formula Syntax – FINDITEM(List, text formatted line item)
ERLANG family of calculation functions
Erlang-B and Erlang-C are standard functions used in queuing theory and are often applied for use in call centers. Often times call centers use the Erlang functions to calculate the number of agents required to achieve a target service level. These functions can be applied to other use cases that involve queuing, such as how many lines to open at a grocery store.
The model builder tips and tricks webinar on December 10 will cover many of the new features and benefits in the Anaplan 2015.5 release. Register to attend and take your modeling skills to the next level.