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

ArgumentData TypeDescription
Value to offsetNumber, Boolean, date,
time period, list, or text
Value to replace with a value from a different time period.
Offset amountNumber

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 valueSame 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 (optionalKeywordDetermines how the LAG function uses the Substitute value argument.

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

Non-positive behavior keywords

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

SEMISTRICTReturns theValue to offset if the Offset amount is positive or zero. 
STRICTReturns theValue to offset if the Offset amount is positive. In STRICT mode, LEAD applies to the future, and not to current periods. The fill value is returned if either shift < 0 or the future period is beyond model time range.

Syntax example

LAG(Value to offset, 2, 0)


JanFebMarAprilMayJune
Value to
offset

  3,000

    1,000

 2,000

 7,000

2,500

3,000
LAG 1        0         0 3,000 1,0002,0007,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.

Additional information

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

Constraints

You can only use a line item for the Offset amount argument if the Value to offset and Substitute value arguments have the number data type. If they use the Boolean, date, time period, list, or text data type, you must enter a number directly into the Offset amount argument.

Calculation engine functionality differences

In Polaris, you can use numeric expressions for the Offset amount argument when you use a data type other than number for the Value to offset argument. Any number used for the Offset amount argument is rounded to the nearest integer.

In the Classic Engine, if you use a data type other than number for the Value to offset argument, you can only use numbers for the Offset amount argument, not numeric expressions or the results of line items. Any number used for the Offset amount argument is rounded towards zero.

Similar functions

Examples

Example 1

 LAG(Value to offset, 2, 0)


JanFebMarAprilMayJune
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,0002,0007,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)


JanFebMarAprilMayJune
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,0002,0007,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])


JanFebMarchAprilMayJune
Value to
offset

  1

   2

  3

  4

  5

  6
Offset
amount
 0 -1  0  1  0  1
Substitute
value
100200 300 400500 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.