LAG returns a value from a period in the past.

You could use LAG to calculate how this month's earnings compare to last year's monthly earnings.

LAG(Value to offset, Offset amount, Substitute value [, Non-positive behavior])

 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. Offset amount Number Number of periods in the past from which to retrieve a value. Positive values refer to past periods, negative to future ones, and zero the current period. Substitute value Same as Value to offset Value to apply if theOffset amount specifies a period outside of the model's time range. 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 Substitute value argument.

The LAG function returns a result of the same data type as the Value to offset argument.

 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.

LAG(Value to offset, 2, 0)

 Jan Feb Mar April May June Value to offset 3,000 1,000 2,000 7,000 2,500 3,000 LAG 1 0 0 3,000 1,000 2,000 7,000

In this example, the LAG 1 line item contains the formula above. This means it returns the value from two periods before.

Since for Jan and Feb, two periods before the cells are outside of the module’s time range, the formula returns the Substitute value of 0. The function does not contain the Non-positive behavior argument, so the default behavior is NONSTRICT.

If the period LAG specifies is outside of the module's time range, LAG returns the value of the substitute value argument.

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.

In the Classic Engine:

• Any number used for the Offset amount argument is rounded towards zero. A value of NaN is equivalent to 0.
• You can use the LAG function only with a time dimension. In Polaris, you can use LAG 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.

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 April May June 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 LAG(Value to offset, 2, Substitute value) 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 does not contain the Non-positive behavior argument, so it uses the default behavior, NONSTRICT.

In this example, the LAG with constant offset line item contains the formula above. This means it returns line item from two periods before each cell.

If the Substitute value amount falls outside the model time range, the value from the Value to offset row in the current period is used, as shown in Jan and Feb columns. The formula does not contain the Non-positive behavior argument, so it uses the default behavior, NONSTRICT.

 Jan Feb Mar April May June Value to offset 3,000 1,000 2,000 7,000 2,500 3,000 Substitute value 10 1 6 1 2 5 LAG with constant offset LAG(Value to offset, 2, Substitute value) 10 1 3,000 1,000 2,000 7,000

In this example, you can see how the different keywords for the Non-positive behavior argument change the results.

 Jan Feb March April May June 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 LAG(Value to offset, Offset amount, Substitute value, SEMISTRICT) 1 200 3 3 5 5 Strict behavior LAG(Value to offset, Offset amount, Substitute value, STRICT) 100 200 300 3 500 5 Nonstrict behavior LAG(Value to offset, Offset amount, Substitute value, NONSTRICT) 1 3 3 3 5 5