The OFFSET function returns a value from a selected dimension, either preceding or following the current value in that dimension.
Use this function to compare salary data across hierarchical employee levels, analyze salary progression, simulate promotions/demotions, or benchmark pay gaps
Syntax
OFFSET(Value to offset, Offset amount, Substitute value [, List])
Arguments
| Argument | Data type | Description |
| Value to offset | Number, Boolean, date, time period, list, or text | Reference value from which you want to offset or retrieve data, for example, current sales. |
| Offset amount | Number | Number of positions to move from the current reference value:
|
| Substitute value | Same as Value to offset | Value to return if the Offset amount specifies a position outside or beyond the current reference value. |
List (Polaris only) | List | The list over which the function should operate. The target line item must be dimensioned with any dimension compatible with this list. See Calculation engine functionality differences. |
The OFFSET function returns a value of the same data type as the Value to offset argument.
The List argument is optional. If you omit it, the function defaults to the Time dimension. If you include it, you can use any dimension that is compatible with the line item.
Calculation engine functionality differences
- In Polaris, a value of NaN (Not a Number) for the Offset amount argument returns the Substitute value argument. In the Classic Engine, a value of NaN is equivalent to 0.
- In Polaris, you can use OFFSET with any dimension except Versions. In the Classic Engine, you can use this function only with a time dimension.
Syntax example
OFFSET(Base salary, Offset amount, 0, Employee levels)
Additional information
OFFSET is the same as LEAD in the NONSTRICT mode.
Excel equivalent
Examples
| Jan | Feb | Mar | Apr | May | Jun | |
| Fruits | Apple | Peach | Banana | Pear | Fig | Melon |
| Veg | Carrot | Tomato | Cucumber | Onion | Lettuce | Broccoli |
OFFSET(Fruits, -1, Veg) | Carrot | Apple | Peach | Banana | Pear | Fig |
OFFSET(Fruits, 2, Veg) | Banana | Pear | Fig | Melon | Lettuce | Broccoli |
| FY16 | FY17 | FY18 | FY19 | FY20 | |
| Data | |||||
OFFSET(Data, 1, FALSE) |
Referencing salaries across employee levels using a list
| 1 | 2 | 3 | 4 | 5 | |
| Base salary | 30,000 | 45,000 | 65,000 | 85,000 | 110,000 |
| Offset amount | 1 | 1 | -1 | -2 | -3 |
Result
| 45,000 | 65,000 | 45,000 | 45,000 | 45,000 |
In this example, OFFSET uses the hierarchical level list to shift salary values. Positive values move forward through the dimension. Negative values move backward through the dimension. Zero stays at the current position. If the Offset amount points to a position outside the dimension's range, the function returns the Substitute value, here 0.
