LAG returns a value from a preceding position within the specified dimension.
You could use LAG to calculate how this month's earnings compare to last year's monthly earnings.
Syntax
LAG(Value to offset, Offset amount, Substitute value[, Non-positive behavior[, List]])
Arguments
| Argument | Data Type | Description |
| Value to offset | Number, Boolean, date, time period, list, or text | Value to replace with a value from a different time period. See the Calculation engine functionality differences section for the use of a non-numeric argument in the Classic engine. |
| 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. Also used for non-positive offsets if you use the SEMISTRICT or STRICT keywords for the Non-positive behavior argument. |
| Non-positive behavior (optional) | Keyword | Determines how the LAG function uses the Offset amount argument. |
| 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. You must provide a Non-positive behavior keyword to use LAG on a list. See Calculation engine functionality differences. |
The LAG function returns a result 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.
Non-positive behavior keywords
| Keyword | Description |
NONSTRICT | The default keyword if you omit the Non-positive behavior argument. Returns the Value to offset if the Offset amount is positive, negative, or zero. |
SEMISTRICT | Returns the Value to offset if the Offset amount is positive or zero. |
STRICT | Returns the Value to offset if the Offset amount is positive. |
Calculation engine functionality differences
In Polaris:
- Any number used for the Offset amount argument is rounded to the nearest integer. A value of NaN (Not a Number) for the Offset amount argument returns the Substitute value argument.
- You can use LAG with any dimension except Versions. In the Classic Engine, you can use this function only with a time dimension.
In the Classic Engine:
- You can use non-numeric Value to offset arguments, but only when the Offset amount is a constant. Otherwise, an error message displays.
- Any number used for the Offset amount argument is rounded toward zero. A value of NaN is equivalent to 0.
Syntax example
LAG(Value to offset, 2, 0)
Additional information
If the period LAG specifies is outside of the module's time range, LAG returns the value of the substitute value argument.
Examples
Example of different non-positive behavior keywords
In this example, you can see how the different keywords for the Non-positive behavior argument change the results.
| Jan | Feb | Mar | Apr | May | Jun | |
| Value to offset | 1 | 2 | 3 | 4 | 5 | 6 |
| Offset amount | 0 | -1 | 0 | 1 | 0 | 1 |
| Substitute value | 100 | 200 | 300 | 400 | 500 | 600 |
Semistrict behavior
| 1 | 200 | 3 | 3 | 5 | 5 |
Strict behavior
| 100 | 200 | 300 | 3 | 500 | 5 |
Nonstrict behavior
| 1 | 3 | 3 | 3 | 5 | 5 |
General example
In this example, the Lag by two periods line item contains a formula that returns the Value to offset from two periods prior.
| Jan | Feb | Mar | Apr | May | Jun | |
| Value to offset | 3,000 | 1,000 | 2,000 | 7,000 | 2,500 | 3,000 |
| Substitute value | 10 | 1 | 6 | 1 | 2 | 5 |
Lag by two periods
| 10 | 1 | 3,000 | 1,000 | 2,000 | 7,000 |
When the Offset amount specifies a period that falls outside the time range, LAG returns the Substitute value amount, as shown in Jan and Feb columns.
The formula doesn’t contain the Non-positive behavior argument, so it uses the default behavior, NONSTRICT.
Supply chain lead-time cash outflows
Create a cash outflow plan that shifts the inventory order values forward by a 2-month delay (lag), returning 0 if the delay goes before January of the current planning year.
Cash outflow module
Create a Supplier Payment Forecasting module with these properties:
- Columns: Time (in months)
- Rows: Line items
- Inventory Orders Placed (Number format)
- Lead Time Offset (Number format)
- Projected Cash Outflow (Number format)
Set your Lead Time Offset to 2 across all months and type in sample order values in the Inventory Orders Placed line item.
| Jan | Feb | Mar | Apr | May | |
| Inventory Orders Placed | 50,000 | 80,000 | 60,000 | 40,000 | 90,000 |
| Lead Time Offset | 2 | 2 | 2 | 2 | 2 |
Projected Cash Outflow
| 0 | 0 | 50,000 | 80,000 | 60,000 |
