ROLLUP
Category: Dimensionality & hierarchies
Syntax Change in Version 7.1: This function no longer requires dimension names, only level names. Scroll to Version History for details.
Overview
The ROLLUP function aggregates values by reducing a node to only the specified levels. All other levels are removed and their values are aggregated.
Use this function when you want to calculate totals or summaries across dimensions while keeping only the levels relevant for your analysis.
If no levels are specified, ROLLUP aggregates the entire node into a single number.
Syntax
From 7.1
ROLLUP('Node', "Level1" [, "Level2", ...] [, "AggregationType"])
Example usage: ROLLUP('Sales', "Year")
Before 7.1
ROLLUP('Node', "Dimension1", "Level1" [, "Dimension2", "Level2", …] [, "AggregationType"])
Example usage: ROLLUP('Sales', "Time", "Year")
Parameters
| Parameter | Description | Type | Required | Default |
|---|---|---|---|---|
| Node | Input node, specified in single quotes (e.g. 'Sales') | Node reference | Yes | — |
| Level1, Level2, … | Level names to keep in the output, specified in double quotes (e.g. "Year") | Level name | No | — |
| AggregationType | Aggregation method for combining values | String | No | Node’s own aggregation type (typically "SUM") |
See also:Aggregation Settings for all available aggregation methods
Output Shape
| Aspect | Behavior |
|---|---|
| Dimensionality | Reduces to only the specified levels |
| Removed levels | All other levels are aggregated |
| Row count | Reduced |
| No levels specified | Returns a single number |
Watch Out
- ROLLUP removes all levels that are not explicitly specified.
- If no levels are provided, the result is a single aggregated value.
- The specified levels must exist in the input node.
- You can specify at most one level per dimension.
- The specified level cannot be more granular than the levels present in the input node.
Examples
Input node: Sales
Describes quantity of products sold across time and categories.
| Product Group | Product | Year | Month | Value |
|---|---|---|---|---|
| Cars | Model T | 2025 | 2025-01 | 11 |
| Cars | Model T | 2025 | 2025-03 | 5 |
| Motorcycles | V-Twin | 2026 | 2026-02 | 7 |
| Motorcycles | 101 Scout | 2026 | 2026-01 | 19 |
Rolling up to a single total
Aggregates all values into one result.
Formula: ROLLUP('Sales')
| → ROLLUP Result |
|---|
| 11 + 5 + 7 + 19 = 42 |
Rolling up with average
Uses AVG instead of the default SUM.
Formula: ROLLUP('Sales', "AVG")
| → ROLLUP Result |
|---|
| (11 + 5 + 7 + 19) / 4 = 10.5 |
Rolling up to specific levels
Keeps only Year and Product Group, aggregates everything else.
Formula: ROLLUP('Sales', "Year", "Product Group")
| Product Group | Year | → ROLLUP Result |
| Cars | 2025 | 11 + 5 = 16 |
| Motorcycles | 2026 | 7 + 19 = 26 |
Related Functions
| Function | When to use instead |
|---|---|
| ROLLUP_TO | When you want to match another node’s dimensionality automatically. |
| DROPLEVEL | When you know which levels to remove rather than keep. |
| EXPAND | When you need to add levels instead of removing them. |
Version History
| Version | Change |
|---|---|
| 7.1 | Syntax simplified to level names only. Dimension names are no longer required or validated. |
Pre-7.1 Syntax (deprecated)
The previous syntax required dimension-level pairs:
ROLLUP('Node', "Dimension1", "Level1" [, "Dimension2", "Level2", …] [, "AggregationType"])
Example (deprecated):
ROLLUP('Sales', "Time", "Year", "Product", "Product Group")
Migration: Remove all dimension names, keep only level names:
ROLLUP('Sales', "Year", "Product Group")