Description
Returns an expression of type Dimension List where values are items from the original List shifted by a positive or negative integer offset.
Syntax
SHIFT(Block, Offset)
Blockis a Metric or List that contains a Dimension List that should be shifted.Offsetis the offset of the shift: use a positive number to shift ahead by N items, or a negative number to shift behind by N items.
Return type
Dimension List
Example
Formula = Employee.StartingMonth
| Alice | Bob | Carl | Dave | |
|---|---|---|---|---|
| Formula | Mar | Jun | Jan | Apr |
Formula = SHIFT(Employee.StartingMonth, 1)
| Alice | Bob | Carl | Dave | |
|---|---|---|---|---|
| Formula | Apr | Jul | Feb | May |
Formula = SHIFT(Employee.StartingMonth, -2)
| Alice | Bob | Carl | Dave | |
|---|---|---|---|---|
| Formula | Jan | Apr | Feb |
Advanced Examples
The SHIFT function is powerful when the offset is dependent on another Dimension/List property.
Offset Metric (Integer Metric defined on team Dimension)
| Offset | |
|---|---|
| Sales | 3 |
| R&D | 1 |
| HR | 1 |
Example on list:
Here, an 'Employee' List, 'Team' and 'Start Month' Properties are Manual inputs. 'Full Ramp-up month' is a Property computed with the formula:SHIFT(Employee.'Start Month','Offset'[by: Employee.Team])
| Team | Start Month | Full Ramp-up month | |
|---|---|---|---|
| Alice | Sales | Apr | Jul |
| Bob | R&D | Jan | Feb |
| Carl | HR | Feb | Mar |
Example on metric:
FTE by team
| Jan | Feb | Mar | Apr | May | Jun | |
|---|---|---|---|---|---|---|
| Sales | 1.5 | 2 | 2 | 3 | 3 | 3 |
| R&D | 5 | 6 | 8 | 8 | 8 | 8 |
| HR | 1 | 1 | 1 | 1 | 1 |
Offset-ed FTE
'FTE by team'[by constant: SHIFT(Month, -'Offset')]
| Jan | Feb | Mar | Apr | May | Jun | |
|---|---|---|---|---|---|---|
| Sales |
| 1.5 | 2 | 2 | ||
| R&D | 5 | 6 | 8 | 8 | 8 | |
| HR | 1 | 1 | 1 | 1 |
See also:
More of a hands-on learner?
Talk to your Customer Success Manager about downloading the Functions and Modifiers in Pigment Application into your workspace. It includes examples of every formula and modifier in Pigment!
