Never use SUM and LOOKUP in the same formula. This can lead to extremely long calculation times. For more information, see Formulas and their effects on model performance .
The SUM aggregation function sums values in a result module based on mapping from a source module.
For example, you can use the SUM function to sum revenue across several product categories.
Syntax
Values to sum[SUM: Mapping, SUM: Mapping 2, etc.]
Arguments
| Argument | Data type | Description |
| Values to sum | Number | The values to sum based on the Mapping argument. |
| Mapping | List, date, time period | The mapping that determines which values to sum. Each instance of this argument must be a dimension present in the Values to sum (source) argument. To provide multiple mappings, repeat this argument and separate each with a comma. |
The SUM function returns a numeric result.
Syntax example
'Employee Expenses'.Salary[SUM:'Employee Details'.Region, SUM: 'Employee Details'.Role]
In this example, the values from the Salary line item are summed for each employee role and region.
Additional information
- Target module dimensions: The line item that contains the SUM function must be dimensioned by the target lists that correspond to the Mapping arguments.
- Source module dimensions: The Mapping arguments must share the same source dimensions as the Values to sum numeric line item.
- SUM as a summary method: SUM can be configured as a line item’s default Summary Method. This is supported on line items with the Number data type to automatically aggregate child values up to parent totals within a hierarchy.
- Data type restrictions: You can use the SUM function to pull data from one module into another. However, the SUM function only aggregates Number data types. To aggregate other data types across dimensions, use the appropriate alternative function:
- Text, Date, or List-formatted: Use FIRSTNONBLANK or LASTNONBLANK
- Boolean: Use ANY or ALL
- Text concatenation: Use TEXTLIST
- Users list referencing: You can reference the system’s Users list as a mapping dimension within the SUM function. However, to maintain data integrity and compliance, you can’t hardcode references to specific users because user lists are treated as dynamic production data.
- SUM or LOOKUP: To decide whether to use SUM or LOOKUP, see Formula usage tips.
Excel equivalent
Examples
Sum sales data across multiple lists and dimensions
Source module
Target module 1: Sales by region and department
Target module 2: Sales by region and time
