EXPAND
Category: Dimensionality & hierarchies
Overview
The EXPAND function expands a node by creating a full cross product of all existing rows with all values of the specified level(s).
Use this function when you need to create an empty data room (a set of rows with placeholder values) that can serve as a driver inside ROLLFORWARD_ADVANCED, or when you want to expand an existing node with additional levels.
Syntax
EXPAND('Node', "Level1" [, "Level2", ...])
Example usage: EXPAND('Growth Rate', "Product")
Parameters
| Parameter | Description | Type | Required |
|---|---|---|---|
| Node | Input node or scalar value, specified in single quotes (e.g. 'Growth Rate') or as a number (e.g. 0) | Node reference or number | Yes |
| Level1, Level2, … | Level names to expand, specified in double quotes (e.g. "Product") | Level name | Yes |
Output Shape
| Aspect | Behavior |
|---|---|
| Dimensionality | Adds the specified levels (and their upper levels in the dimension hierarchy) to the result |
| Values | Existing values are duplicated across all members of the new level(s). Scalar inputs fill every cell with that value. |
| Row count | Full cross product |
Watch Out
- EXPAND creates the full cross product of all values in the specified level(s). If you only need specific values, use EXPANDSINGLE instead.
- Expanding a node copies each row’s value into every member of the new level. Be aware this changes totals. For example, if a row has value 100 and you expand across 3 products, the sum becomes 300 instead of 100.
Examples
Expand a scalar to full dimensionality
Initialize an empty simulation node with zeros across all years, regions, and products.
Formula: EXPAND(0, "Year", "Region", "Product")
| Year | Region | Product | → EXPAND Result |
|---|---|---|---|
| 2025 | EMEA | Alpha | 0 |
| 2025 | EMEA | Beta | 0 |
| 2025 | APAC | Alpha | 0 |
| 2025 | APAC | Beta | 0 |
| 2026 | EMEA | Alpha | 0 |
| … | … | … | … |
The scalar 0 is replicated across every combination of Year × Region × Product.
The result makes the simulation node eligible for use with ROLLFORWARD.
Expand a node to add a dimension
A growth-rate node only has a Year level, but should also include the Product level.
Input node: Growth Rate
| Year | Value |
|---|---|
| 2025 | 0.03 |
| 2026 | 0.05 |
Formula: EXPAND('Growth Rate', "Product")
| Year | Product | → EXPAND Result |
|---|---|---|
| 2025 | Alpha | 0.03 |
| 2025 | Beta | 0.03 |
| 2026 | Alpha | 0.05 |
| 2026 | Beta | 0.05 |
The same growth rate per year is now available for each product.
Expand into a finer level within an existing dimension
A revenue node already has a "Region" level. You want to break it down to individual countries, a finer level in the same dimension hierarchy.
Input node: Revenue Target
Year | Region | Value |
|---|---|---|
2025 | EMEA | 500 |
2025 | APAC | 300 |
Total | 800 | |
Formula: EXPAND('Revenue Target', "Country")
Year | Region | Country | → EXPAND Result |
|---|---|---|---|
2025 | EMEA | Germany | 500 |
2025 | EMEA | France | 500 |
2025 | APAC | Japan | 300 |
2025 | APAC | Australia | 300 |
Total | 1600 | ||
Because "Country" is a finer level below "Region" in the hierarchy, EXPAND adds only the child values belonging to each region, not a full cross product of all countries with all regions.
Note that the values are duplicated into each child row, so totals will increase.
Related Functions
| Function | When to use instead |
|---|---|
| EXPANDSINGLE | When you only need specific level values instead of a full cross product. |
| DROPLEVEL | When you need to remove levels instead of adding them. |
| ROLLUP | When you want to aggregate to fewer levels. |
| ROLLFORWARD_ADVANCED | When you need to project expanded driver nodes forward in time. |