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

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

How to use “SUM” function in Anaplan

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.

Image by author made with PPTX

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

Image by author Aykut Coskun made by screenshot in Anaplan

Image by author Aykut Coskun made by screenshot 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.

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.

Image by author Aykut Coskun made by screenshot 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”

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

To find the other articles on use of functions through examples please visit my profile.