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.
- A constant offset with decimal places is cut back to a whole number, so
1.7shifts by 1. - A node used as the offset must contain whole numbers only. A single value with decimal places makes the calculation fail with an error rather than being rounded.
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 |