Anaplan Formulas for Model Builders | Tips and Tricks

AUTHOR

Vicky Ascencio

Vicky Ascencio is a Client Director on the Customer Success team at Anaplan and has been with the company since 2013. In her tenure with Anaplan she has focused on enabling and empowering customer teams, both big and small. Her favorite part of the job is helping a customer define their Anaplan journey and supporting them as they execute. This is her second startup in very different industries; she loves the fast-paced nature of disruptive companies.

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.

TEXTLIST
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]

MOVINGSUM
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)

RANKCUMULATE
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.

tips2

Formula Syntax – RANKCUMULATE(CumulationSource,Rank Source,Direction,Include,Group)

FINDITEM
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.

tips1

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.

Explore the benefits of planning and modeling on the Anaplan Platform

Learn more