Rollup

The Rollup function performs a lookup and returns an aggregation. It can perform a lookup in another data element, or a self-lookup within the same element.

To perform a rollup calculation without manually constructing the formula in the formula bar, see Add Columns via Lookup.

Syntax

Rollup(formula, local key 1, external key 1, [local key 2], [external key 2], ...)

Function arguments:

• formula (required) - The formula to compute (or the target column to reference) for the row in the target element.
• local key 1 (required) - The column to use as a join key in the local data element.
• external key 1 (required) - The column to use as a join key in the target data element.
• local key 2 + (optional) - An additional column to use as a join key in the local data element.
• external key 2 + (optional) - An additional to use as a join key in the target data element.

Examples

Rollup(Sum([Sales Amounts/Sales Amount]), [Order Number], [Sales Amounts/Order Number])

For each row in the Orders table, returns the sum of [Sales Amount] for all rows in the Sales Amounts table with the corresponding [Order Number].

For example, in the Sales Amounts table, there are two rows with an [Order Number] of 130. The sum of the [Sales Amount] for those two rows is 360, which is returned for the row in the Orders table with an [Order Number] of 130.

Rollup(Avg([Profit]), [Order Number], [Order Number])

For each row in the Orders table, returns the average of [Profit] for all rows in the Orders table with the corresponding [Order Number].

This is an example of a self-rollup, where the table is joined to itself in order to present summary data from across multiple rows in each individual row. In this case, the Orders table contains multiple rows for each order, with different profit margins for each item in the order. The self-rollup uses those profit margins to calculate the average profit margin for each order. This could be used to identify items that are consistently more or less profitable than items they are commonly ordered with.