The OFFSET function returns a value from a different time period, either before or after the current period.
Use this function to compare current sales with past sales.
Syntax
OFFSET(Value to offset, Offset amount, Substitute value)
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. |
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.
- You can use the OFFSET function only with a time dimension. In Polaris, you can use OFFSET with any dimension except Versions.
- If the Expression argument has a data type of list or time period, and does not resolve to a different data type, the result line item must have the same data type.
Syntax example
OFFSET(Current sales, -1, 0)
| Jan | Feb | Mar | Apr | May | Jun | |
| Current sales | 3,000 | 4,000 | 4,500 | 3,700 | 3,500 | 4,100 |
| Last month's sales | 0 | 3,000 | 4,000 | 4,500 | 3,700 | 3,500 |
The formula OFFSET(Current sales, -1, 0) is applied to the Last month's sales line item. In this formula:
- Current sales is the reference value (Value to offset).
- -1 is the number of periods to move from the current period (Offset amount). Negative 1 represents one month before the current period.
- 0 is the value to substitute (Substitute value), when there's no data available at the specified period.
Additional information
OFFSET is the same as LEAD in the NONSTRICT mode.
Constraints
The Value to offset argument and result line item must share the same time range. Any formula that conflicts with this rule is rejected.
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) |
