1. Calculation functions
2. All Functions
3. Time and Date Functions
4. LAG

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.

## Syntax

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

## 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. Offset amount Number Number of periods past, from which to retrieve a value. Positive values refer to past periods, negative to future ones and Zero is the current period. Substitute value Mirrors Value to offset data type 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 if the LAG function should use the Substitute value instead if the Offset amount is negative or zero.

Note: The LAG keywords are NONSTRICT, SEMISTRICT, or STRICT.

## Syntax example

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.

### Keywords for non-positive behavior argument:

The LAG mode is set by the keywords, NONSTRICT, SEMISTRICT, or STRICT. The mode controls whether LAG applies to the current period and all past periods, or past periods only.

• NONSTRICT: 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. In STRICT mode, LAG applies to the past, and not to current periods. The fill value is returned if either shift < 0 or the past period is beyond model time range.
• If the period LAG specifies is outside of the module's time range, LAG returns the substitute value.
• If a keyword is not included, the behavior defaults to NONSTRICT.

Note: LAG is the opposite of LEAD. Yet the two functions can achieve the same result. If the offset amount of LAG is negative, then the result is the same as LEAD with a positive offset.

## Detailed examples

### Example 1

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 Substitute value 10 1 6 1 2 5 LAG 0 0 3,000 1,000 2,000 7,000

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

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 function does not contain the Non-positive behavior argument, so the default behavior is NONSTRICT.

### Example 2

LAG(Value to offset, 2, Substitute value)

 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 10 1 3,000 1,000 2,000 7,000

In this example, the LAG 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 function does not contain the Non-positive behavior argument, so the default behavior is NONSTRICT.

### Example 3

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

 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 LAG SEMISTRICT 1 200 3 3 5 5 LAG STRICT 100 200 300 3 500 600 LAG NONSTRICT 1 3 3 3 5 5

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