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 periods to move from the current reference value:
|
| Substitute value | Same as Value to offset | Value to return if the Offset amount specifies a period outside or beyond the model's time range. |
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.
This function allows for three arguments. If you have these functions with two arguments and don't declare a third one, this will default to Time. If you declare a third argument, then you can use any related dimension to the line item as an argument.
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 Offset amount shifts forward while the negative shifts backward. If the offset goes out of bounds, it returns 0, the Substitute value.
