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.
Answers
-
Really like this. Thank you for pulling this all together. One pathway that seems that you might consider accounting for is the SUM function exception where the TARGET module has a parent of one of the SOURCE module lists. Like G1 Region (TARGET) and G3 Location (SOURCE). In this case, we would SUM but we don't need to actually use it since the relationship is implied in the structured list. Your number 5 process is close but I'm not sure how this scenario is accounted for.
Love this. More, more!
1 -
Thank you @JaredDolich @for your valuable feedback regarding my post.
What you shared will help me improve my current and next studies. I appreciate your time and guidance.
Aykut Coskun
2 -
Suoperb Explanation...
1
