Anaplan provides numerous calculation functions which deliver enhanced analysis within a model. Two common and related functions are SUM and LOOKUP. SUM enables the summation of values based on a list or time formatted line item or property in a source with a matching dimension in a target. SUM achieves this through user-entered mappings within function parameters to determine criteria for the values in the source to be summed. LOOKUP is used when mapping from one cell or value to many, and SUM is used in contrast, summarizing multiple cells into one.
Below is the syntax for the SUM function:
x[SUM: y]
‘X’ is referring to the column holding the values to SUM.
‘Y’ is referring to the columns to be used as a cross-reference. You may have multiple cross-references. x[SUM: y, SUM: y_1, …]
The key to doing this is ensuring that your ‘x’ value column is of number format, and your ‘y’ value column is a list-formatted or time period-formatted line item or property.
Looking at an example, let’s say we have three modules; the first, ‘Product Details,’ is set up to describe products (product code, product size or volume, the category in which the product belongs, etc.).
The second module ‘Revenue Stats’ contains revenue statistics according to product and geography.
In the third module, ‘Size Revenue Report,’ which will be the module we use to perform a SUM function, we would like to SUM the revenue made per month according to the size of the products being sold. The formula would be entered into revenue line item in the Size Revenue Report module and would be as follows:
‘Revenue Stats’.Revenue[SUM: ‘Product Details’.Size]
LOOKUP is used to look up a number, Boolean, time-period, list item, text, or date value in either a list or time-period from a source module according to common mappings, of which you may use multiple. This is like the SUM function; however, it does not contain an inherent aggregation in its usage. The syntax is as follows:
x[LOOKUP: y]
An example of LOOKUP could be as follows:
Let’s say you have two modules; one module ‘Bonuses Table,’ containing a line item ‘Bonuses’ with ‘Region of Employment’ and ‘Job Position’ dimensions with inherent bonuses data associated with those dimensions, and a second module, ‘Employee Details,’ containing ‘Employee Names’, and blank fields for ‘Region of Employment’ and ‘Job Position’. If you wanted to map an employee’s bonus according to their ‘Region of Employment’ and ‘Job Position’, the LOOKUP formula used would be as follows:
‘Bonuses Table’.Bonuses[LOOKUP: Region of Employment, LOOKUP: Job Position]
How then would you consider some general rules for deciding which function to use? Anaplan provides the general rule of thumb in the Anapedia: 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 juxtaposition if your mapping table contains the Source list and maps to a Target list-formatted line item or list-formatted property then use SUM.
Ask yourself before writing a formula containing either SUM or LOOKUP, where are you writing the formula (Target)? Where is the data residing currently (Source)? How am I achieving my mapping (use the general rule of thumb)?