How to use “SUM” function in Anaplan: Explanation Through an Example

Hello Anaplan Community,

During my Level-I and Level-II trainings I thought that it would be useful if an explanation with an detailed simulation for use of LOOKUP and SUM functions. There for I prepared a detailed explanation for each functions. Below you can find the explanation with an detailed simulation for use of SUM function. I hope this will help you understand the use of it.

Determining

There are several ways to pull data from one module to another. One of them is “SUM” function. SUM function is possible under some conditions.

· Aggregating or dimensionalizing the data is needed,

· Target module has additional dimension that do not have parent/child relationship with the source model,

· The SUM function only works with the number values. Hence the source & target line item must be numbered format line item.

Using the SUM function requires correct mapping. Choosing the mapping module or mapping list according to the right criteria while performing mapping is important for the correct design of the formula.

Mapping

The mapping determines which values to sum. A mapping table between the Source List and Target List can be set up in one of two places:

· in a module, using a list-formatted line item

· in a list, using a list-formatted property.

Syntax: Values to sum[SUM: Mapping 1, SUM: Mapping 2, etc.]

The target module - Screenshot by author Aykut Coskun in Anaplan.

The source module - Screenshot by author Aykut Coskun in Anaplan.

When deciding which module to choose for mapping, a module should be chosen that will associate the different dimensions between the target and source modules with each other. Then it is necessary to choose the right line item.

If your mapping table contains the Source list and maps to a Target list-formatted line item or list-formatted property, then use SUM function in your formula. Setting your mapping table up this way says that each Target item can map to many Source items, so we are summing up all Source data that is to land in each Target list item. This is similar to SUMIF in Excel.

Note: If your mapping table contains the Target List and maps to a Source list-formatted line item or list-formatted property, then use LOOKUP in your formula. Setting your mapping table up this way says that each Target item can map to one and only one Source list item in the mapping table, so we are looking up that Source item value.

Image by author made with PPTX

Let's consider our example. If we look at the image shown above, it can be seen the source model and target module have different dimensionality. Source module is “REV03”. We need “Cost of Sales” data to be pulled into “REP02” module. We already have this data in source module. which can be seen below.

The source module - Screenshot by author in Anaplan.

If we look at the dimensions, it can be seen that the source module has “G3 Location” as a dimension. The target module has “G2 Country” as a dimension, which is parent list of the “G3 Location”. This means that, for this dimension mapping is not required (refer to Decision Algorithm box number 4). The source module has also “P2 Product” as a dimension. On the other hand, the target module has “Size” as a dimension. Thus, “Cost of Sales” data sorted by “P2 Product” list is required to be sorted by “Size” list. For this reason, we need mapping module, which connects “P2 Product” and “Size” data and contains “Size” line item. 

The formula will be like shown below.

Target Line Item Formula: ‘REV03’.Cost of Sales[SUM: ‘SYS06 Product Details’.Size] 

Image by author Aykut Coskun made with PPTX “Decision Algorithm for Anaplan”

To see the article on "Decision Algorithm for a selection of correct functions in Anaplan" please click here.