SHIFT
Category: Compare periods
Overview
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 when you need a custom offset on a specific level instead of a fixed previous-period helper or a rolling aggregation.
Syntax
SHIFT('Node', "LevelName", ShiftAmount)
SHIFT('Node', "LevelName", 'ShiftNode')
Example usage: SHIFT('Revenue', "Year", 1)
Parameters
Parameter | Description | Type | Required |
|---|---|---|---|
Node | Input node that is shifted, specified in single quotes (e.g. | Node reference | Yes |
Level | The level along which data is shifted, specified in double quotes (e.g. | Level name | Yes |
ShiftAmount / ShiftNode | The offset to shift by. Can be a constant number or a node reference. | Number or Node reference | Yes |
Output Shape
Aspect | Behavior |
|---|---|
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.
A positive ShiftAmount moves values to earlier periods. Use a negative value to project values into future periods.
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 |
Related Functions
Function | When to use instead |
|---|---|
When you need a fixed one-year shift for year-over-year comparisons without a custom offset. | |
When an effect should appear gradually or with a delay across time periods. | |
When you want to aggregate values over a rolling window instead of moving them to different positions on a level. |