EXPANDSINGLE
EXPANDSINGLE('Node', "Level1", Value1 [, "Level2", Value2, ...])
The EXPANDSINGLE function expands a node by adding specific level values for one or more levels. For each specified level-value pair, the function creates rows containing the value from the input node.
Use this function when you want to assign a value to a specific dimensional combination, such as creating entries for a particular year, region, or product: EXPANDSINGLE('Sales', "Year", "2025").
Parameters
- NodeNode referenceRequired
- Input node, specified in single quotes (e.g.
'Sales') - Level1, Level2, …Level nameRequired
- Level on which the node should be expanded, specified in double quotes (e.g.
"Year") - Value1, Value2, …Level value / Level value listRequired
- Level value used for expansion, specified in double quotes. Can be a single value or a list of values (e.g.
"2025"or["2025", "2026"])
Multiple Level–Value pairs can be provided to expand across multiple dimensions.
Output shape
- Dimensionality
- Adds the specified level(s) to the result
- Values
- Each created row contains the value of the input node
- Row count
- One row per specified level-value combination
Watch out
- Unlike EXPAND, EXPANDSINGLE creates rows only for the explicitly specified level values, not for the full cross product.
- The specified level values must exist in the hierarchy.
- When a list of values is provided, one row is created for each value.
- You cannot expand on a level whose dimension already exists on the input node at the same or coarser granularity. You can only expand by a more fine-grained level in that dimension.
Examples
Expand a value to a specific year
Input node: 'Node'
| Value |
|---|
| 1.1 |
Formula: EXPANDSINGLE('Node', "Year", "2026")
| Year | EXPANDSINGLE Result |
|---|---|
| 2026 | 1.1 |
The value is assigned to the Year 2026.
Expand across multiple levels
Formula: EXPANDSINGLE('Node', "Year", "2026", "Product Group", "Cars")
| Year | Product Group | EXPANDSINGLE Result |
|---|---|---|
| 2026 | Cars | 1.1 |
The value is expanded across two dimensions simultaneously.
Expanding to multiple values
You can also provide a list of values.
Formula: EXPANDSINGLE('Node', "Year", "2026", "Product Group", ["Cars", "Trucks"])
| Year | Product Group | EXPANDSINGLE Result |
|---|---|---|
| 2026 | Cars | 1.1 |
| 2026 | Trucks | 1.1 |
Each value in the list creates a new row.
One-time tax relief in 2026
Input node: 'Base Taxation'
| Year | Value |
|---|---|
| 2025 | 1,000 |
| 2026 | 1,000 |
| 2027 | 1,000 |
| 2028 | 1,000 |
Input node: 'Relief Value'
| Value |
|---|
| 500 |
Formula: EXPANDSINGLE('Relief Value', "Year", "2026")
Output node: 'Relief Expanded'
| Year | EXPANDSINGLE Result |
|---|---|
| 2026 | 500 |
Calculation: 'Base Taxation' - 'Relief Expanded'
| Year | Value |
|---|---|
| 2025 | 1,000 |
| 2026 | 500 |
| 2027 | 1,000 |
| 2028 | 1,000 |
The relief only affects 2026, because that is the only expanded year.
Example with hierarchical levels
If a hierarchy exists, specifying the lowest level is sufficient.
Example hierarchy
Product Group → Product Line → Product
Input node: 'Quantity'
| Value |
|---|
| 500 |
Formula: EXPANDSINGLE('Quantity', "Product", "Max T 3")
| Product Group | Product Line | Product | EXPANDSINGLE Result |
|---|---|---|---|
| Cars | Sedans | Max T 3 | 500 |
Because “Max T 3” belongs to the product line “Sedans,” which in turn belongs to the product group “Cars,” Valsight automatically fills in these parent levels in the result. You only need to specify the most detailed level.
Using a project variable as the level value
Instead of hardcoding a year, you can use a project variable so the formula updates automatically when the variable changes.
Formula: EXPANDSINGLE('Relief Value', "Year", "$PLANNING_START")
This creates a row for the year currently defined by $PLANNING_START, no formula change needed when the planning horizon shifts.
See also: Project Variables