EXPANDSINGLE
Category: Dimensionality & hierarchies
Overview
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.
Syntax
EXPANDSINGLE('Node', "Level1", Value1 [, "Level2", Value2, ...])
Value can be a single level value in double quotes or a list of level values in square brackets.
Example usage: EXPANDSINGLE('Sales', "Year", "2025")
Parameters
Parameter | Description | Type | Required |
|---|---|---|---|
Node | Input node, specified in single quotes (e.g. | Node reference | Yes |
Level1, Level2, … | Level on which the node should be expanded, specified in double quotes (e.g. | Level name | Yes |
Value1, Value2, … | Level value used for expansion, specified in double quotes. Can be a single value or a list of values (e.g. | Level value / Level value list | Yes |
Multiple Level–Value pairs can be provided to expand across multiple dimensions.
Output Shape
Aspect | Behavior |
|---|---|
Dimensionality | Adds the specified level(s) to the result |
Row count | One row per specified level-value combination |
Values | Each created row contains the value of the input node |
Watch Out
Unlike
EXPAND,EXPANDSINGLEcreates 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
Node: Base Taxation
Year | Value |
|---|---|
2025 | 1,000 |
2026 | 1,000 |
2027 | 1,000 |
2028 | 1,000 |
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