Search

SUM

Sum values based on a list- or time period-formatted line item or property in the source that matches a dimension in the target. Each mapping is a line item or property with data type specified to be list values from one of the dimensions of the result line item. SUM works by using the mappings you enter for the function parameters as a set of determining criteria for the values in the source to be summed in the result.

To determine whether to use SUM or LOOKUP, see Formula Usage Tips.

You can use SUM to pull data from one module into another. If you need to do this for other data formats, such as Boolean-formatted or text-formatted line items, use a different aggregation function.

Syntax

x[SUM: y]

where:

  • x: Column holding the values to SUM.
  • y: Columns to use as cross references.
    • Multiple cross references are allowed.
    • x[SUM: y, SUM: z, ...]

Format

Input Format Output Format

x: Number

y: List, Time period

Matches the data format used by argument x

Arguments

The function uses the following arguments:

  • x: Number: Numeric line item, property, or expression.
  • y: Line item: List-formatted or time period-formatted line item or property.

Constraints

The function has the following constraints:

  • Line items used for mappings must share a dimension with the source line item.
  • Result line item must be number-formatted.
  • Result line item must have each list used for mapping applied to it as a dimension.

Excel equivalent

  • SUMIF

Back to Top

Example

Transaction Module

The column headers are line items. Product and Location are list-formatted.

 

Product

Location

Sales

From Date

Transaction 1

Apples

London

12000

01/01/2012

Transaction 2

Bananas

London

22000

01/01/2012

Transaction 3

Pears

London

32000

01/01/2012

Transaction 4

Apples

Paris

12000

01/01/2012

Transaction 5

Bananas

Paris

22000

01/01/2012

Transaction 6

Pears

Paris

32000

01/01/2012

SUM total sales for one item

The Result module has Products on Rows and line item Sales as Columns. The Sales column uses this function syntax.

Transaction.Sales[SUM:Transaction.Product]        

How the function works in this example

The function to sum all values from the Transaction Module Sales column by matching values from the Product column.

SUM total sales for one region

You can use more than one SUM function to filter the data precisely. Note the From Dates are different in this source module.

  Product Location Sales From Date

Transaction 1

Apples

London

12000

01/01/2012

Transaction 2

Bananas

London

22000

02/01/2012

Transaction 3

Pears

London

32000

03/01/2012

Transaction 4

Apples

Paris

12000

01/01/2012

Transaction 5

Bananas

Paris

22000

03/01/2012

Transaction 6

Pears

Paris

32000

02/01/2012

The Result Module requires configuring with dimensions to match the additional variables. The Results Module has Products on Rows and Organization as Columns. The other function variable, Sales, is configured as Pages. The Sales cells use this function syntax.

Transaction.Sales[SUM: Transaction.Item, SUM: Transaction.Location, SUM: Transaction.From Date]        

How the function works in this example

The function uses the Transaction Module to sum all values from the Sales column by matching values from the Product, Organization, and Date columns.

Example code

  • To SUM the Products in the Sales column of the Transaction Module; where Products and Sales are dimensions common to both modules.
    Transaction.Sales[SUM:Transaction.Product]
  • To SUM the Products in the Sales column of the Transaction Module filtering by Date; where Products, Sales, and Date are dimensions common to both modules.
    Transaction.Sales[SUM:Transaction.Product,SUM:Transaction.Date]
  • To SUM the Products in the Sales column of the Transaction Module filtering by Location and Date; where Products, Sales, Location, and Date are dimensions common to both modules.
    Transaction.Sales[SUM:Transaction.Product, SUM:Transaction.Location, SUM:Transaction.Date]

Back to Top

Similar functions