SHIFT
SHIFT('Node', "LevelName", ShiftAmount)
SHIFT('Node', "LevelName", 'ShiftNode')
The SHIFT function shifts the input node by a given offset amount on a specified level. The shift amount can be a constant number or a node that provides different offsets per entry.
Use this function when you need a custom offset on a specific level instead of a fixed previous-period helper or a rolling aggregation: SHIFT('Revenue', "Year", 1).
Parameters
- NodeNode referenceRequired
- Input node that is shifted, specified in single quotes (e.g.
'Profit') - LevelLevel nameRequired
- The level along which data is shifted, specified in double quotes (e.g.
"Year"). The input node must have data on this level. - ShiftAmount / ShiftNodeNumber or Node referenceRequired
- The offset to shift by. Can be a constant number or a node reference.
Output shape
- Dimensionality
- Preserved (same levels as input)
- Values
- Repositioned along the specified level by the offset amount
- Row count
- May decrease if shifted values fall outside the level bounds
Watch out
- A positive shift amount moves values to earlier periods. A negative value moves them to later periods. This is the opposite of what many users expect.
- For non-time dimensions, shifting is only allowed on the lowest level in the hierarchy.
- Shifting on linked (extended) levels is not supported.
- When using a node as the shift amount, its levels must match the input node’s levels.
- Only whole-number offset values are considered.
Examples
Shift on the Year dimension (positive vs. negative)
This example illustrates how the sign of the ShiftAmount controls direction.
Input node: 'Revenue'
| Year | Revenue |
|---|---|
| 2025 | 100 |
| 2026 | 120 |
| 2027 | 140 |
Formula: SHIFT('Revenue', "Year", 1) (positive → shifts data backwards)
| Year | SHIFT Result |
|---|---|
| 2024 | 100 |
| 2025 | 120 |
| 2026 | 140 |
The 2025 value now appears in 2024. Each value has moved one year into the past.
Formula: SHIFT('Revenue', "Year", -1) (negative → shifts data forward)
| Year | SHIFT Result |
|---|---|
| 2026 | 100 |
| 2027 | 120 |
| 2028 | 140 |
The 2025 value now appears in 2026. Each value has moved one year into the future.
Shift by a constant amount
This example shifts all values on the Contract Age level by the same constant offset.
Input node: 'Contracts'
| Contract Age | Contracts |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
Formula: SHIFT('Contracts', "Contract Age", -1)
| Contract Age | SHIFT Result |
|---|---|
| 1 | |
| 2 | 10 |
| 3 | 20 |
| 4 | 30 |
Shift by values from another node
This example uses a second node to define different shift amounts for each contract type.
Input node: 'Contracts'
| Contract Age | Contract Type | Contracts |
|---|---|---|
| 1 | A | 10 |
| 2 | B | 20 |
| 3 | C | 30 |
Input node: 'Shift Node'
| Contract Type | Amount |
|---|---|
| A | -1 |
| B | -2 |
| C | -3 |
Formula: SHIFT('Contracts', "Contract Age", 'Shift Node')
| Contract Age | Contract Type | SHIFT Result |
|---|---|---|
| 2 | A | 10 |
| 4 | B | 20 |
| 6 | C | 30 |
Shift by offsets defined per level value
This example uses a shift node on the same level that is being shifted, so each contract age gets its own offset value.
Input node: 'Contracts'
| Contract Age | Contract Type | Contracts |
|---|---|---|
| 1 | A | 10 |
| 2 | B | 20 |
| 3 | C | 30 |
Input node: 'Shift Node'
| Contract Age | Amount |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
Formula: SHIFT('Contracts', "Contract Age", 'Shift Node')
| Contract Age | Contract Type | SHIFT Result |
|---|---|---|
| 0 | A | 10 |
| 1 | B | 20 |
| 2 | C | 30 |