When building modules in Anaplan, calculations form the backbone of your models so knowing the most common functions and how to use them is very important. Below are ten of the most common functions model builders will utilize, along with their purpose and syntax.

Before reading this document, it is recommended you understand the basics of Anaplan. The best way to become familiar with Anaplan is through the Anaplan Community website. The website has numerous free resources including guides on best practices, a community blog and training. If you are already an Anaplan user with access to a workspace, you should consider completing the level 1 training course as it includes introductions to all the primary of Anaplan concepts.

The Basics

1) SUM: Source Values to Sum [SUM: List, Time, etc., SUM: etc.]

The SUM function sums values in a target module based on shared dimensionality. For example, you can sum profit across different business units. The calculated value must always be a number. Use SUM instead of LOOKUP when the relationship from source to target is many to one respectively. This function may be combined with LOOKUP or SELECT as well. Anaplan expects a numeric value as the format for a line item containing the SUM function. The SUM function is most like SUMIF or SUMIFS in Excel.

*While it is possible to combine SUM and LOOKUP, it can often lead to performance issues. To avoid these issues, consider adding an intermediate calculations module instead.

2) LOOKUP: Source Value to Lookup [LOOKUP: List, Time, etc., LOOKUP: etc.]

The LOOKUP function is used to look up values in one module using shared dimensionality and return results in another module. For example, you can look up the price of each product in a module based on sales territory and country, then display the prices in a results module. The calculated value returned depends on the format of the source. Use LOOKUP instead of SUM when the relationship from source to target is one to many. A line item using LOOKUP supports multiple line item formats. This function works like LOOKUP in Excel.

*While it is possible to combine SUM and LOOKUP, it can often lead to performance issues. To avoid these issues, consider adding an intermediate calculations module instead.

3) IF THEN ELSE: IF Boolean argument THEN X ELSE Y

This Function tests a true or false argument and returns a value based on the outcome. For example, you can decide whether to perform a calculation dependent on a variable. The calculated value returned depends on the format of the source. The Excel equivalent is the IF function.

*Be aware when nesting multiple conditional statements that in some instances there will be performance degradation in which case you should split the conditional statements into multiple line items. Also be sure to calculate a value only once in a module and reference it in multiple modules as needed.

4) ISBLANK: ISBLANK (X)

The ISBLANK function tests whether a line item contains a value or not. If there is no value, it will return as true. For example, you can use the ISBLANK function to determine if a sales territory currently has a sales representative. ISBLANK line items need to be formatted as Booleans. The Excel equivalent is the ISBLANK function.

5) MONTHVALUE: MONTHVALUE (X)

The MONTHVALUE function returns the monthly time summary for a selected line item. For example, you can use this function to identify which products across weeks overperform or underperform by comparing them to the month value. The calculated value returned depends on the format of the source.

6) ITEM: ITEM (List member)

The ITEM function returns the member of a list or time range at a given intersection in a module. The format of the line item for this function must be List or Time. This function is often used when mapping between modules with SUM or LOOKUP. For example, when mapping and summing between modules using an attribute when the source and target modules don’t have the same dimensionality.

7) PARENT: PARENT (X)

The PARENT function displays the parent of a member in a List or Time. The format of the line item for this function must be List or Time. For example, you can use this function to return the sales territory to which a sales representative belongs. The value returned by the function depends on the format of the source. As a best practice, do not nest PARENT functions, instead create separate line items.

8) NAME: NAME (X)

The NAME function converts a list member or time range to text. For example, you can get the sales representative’s name from a list as text to display in a report. The display value returned will always be text format.

9) CODE: CODE (X)

The CODE function returns the code, as defined in the list, for a given list member. For example, you can use the function to display only code associated with employees instead of their name. If the code is not populated in the list a blank value will be displayed.

10) SELECT: Reference Value(s) [SELECT: List Member or Time Period]

The SELECT function is used to return the value of a specific list member from the same or other lists to use in calculations. An appropriate use is to select the two set scenarios or versions (on two separate line items) for comparison and then calculate the variance between them in a third line item. In many instances this prevents adding unnecessary dimensions to a module and thereby reduces the size of the modules. The SELECT function is most like LOOKUP in Excel.

Additional Details

This table provides additional information about the syntax and return values of each of the functions described above.