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!