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, such as current sales. |
| Offset amount | Number | Number of positions to move from the current reference value. Positive moves forward, negative moves backward, zero stays at the current position. |
| 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
| Behavior | Classic | Polaris |
| NaN for Offset amount | A value of NaN is treated as 0 | Returns the Substitute value |
| Supported dimensions | Only time dimension | Any dimension except Versions |
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
Example 1: Quota comparison across territory tiers
Example 2: Salary benchmark across job grades
Example 3: Net income cross-check across consolidation entities
Example 4: Inventory preview for the next replenishment cycle
