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

OFFSET(Value to offset, Offset amount, Substitute value [, List])

ArgumentData typeDescription
Value to offsetNumber, Boolean, date,
time period, list, or text
Reference value from which you want to offset or retrieve data, for example, current sales.
Offset amountNumber

Number of periods to move from the current reference value:

  • Positive values move forward in time, to future periods.
  • Negative values move backward in time, to past periods.
  • Zero stays in the current period.
Substitute valueSame 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.

  • 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.

OFFSET(Base salary, Offset amount, 0, Employee levels)

OFFSET is the same as LEAD in the NONSTRICT mode.


JanFebMarAprMayJun
FruitsApplePeachBananaPearFigMelon
VegCarrotTomatoCucumberOnionLettuceBroccoli
OFFSET(Fruits, -1, Veg)CarrotApplePeachBananaPearFig
OFFSET(Fruits, 2, Veg)BananaPearFigMelonLettuceBroccoli

FY16FY17FY18FY19FY20
Data
OFFSET(Data, 1, FALSE)

12345
Base salary30,00045,00065,00085,000110,000
Offset amount11-1-2-3

Result

OFFSET(Base salary, Offset amount, 0, Employee levels)

45,00065,00045,00045,00045,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.