LEAD returns a value from a period in the future.
LEAD
You could use LEAD to calculate how this month's earnings compare to next year's projected monthly earnings.
LEAD(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 |
The value to replace with a value from a different time period. |
| Offset amount | Number | Number of periods forward from which to retrieve a value. Positive values refer to future periods, negative to past periods, and zero to the current period. |
| Substitute value | Same asValue to offset | Value to return if theOffset amountspecifies a period outside of the model's time range. Also used for non-positive offsets if you use the SEMISTRICT or STRICT keywords for theNon-positive behaviorargument. |
| Non-positive behavior(optional) | Keyword | Determines how the LEAD function uses theSubstitute valueargument. The keywords are NONSTRICT, SEMISTRICT, and STRICT. There's more information in the Non-poisitive behavior keywords section below. |
The LEAD function returns a result of the same data type as theValue to offsetargument.
Non-positive behavior keywords
| Keyword | Description |
| NONSTRICT | The default keyword if you omit theNon-positive behaviorargument. Returns theValue to offsetif theOffset amountis positive, negative, or zero. |
| SEMISTRICT | Returns theValue to offsetif theOffset amountis positive or zero. |
| STRICT | Returns theValue to offsetif theOffset amountis 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
LEAD(Value to offset, 2, 0)
In this example, theLEAD 1line item returns the value from two periods after each cell. If two periods after a cell is outside of the module’s time range, the formula returns theSubstitute valueof 0, as seen in theJunecolumn. The function does not contain theNon-positive behaviorargument, so the default behavior isNONSTRICT．
| Jan | Feb | March | April | May | June | |
| Value to offset |
1 |
2 |
3 |
4 |
5 |
6 |
| LEAD 1 | 3 | 4 | 5 | 6 | 0 | 0 |
Additional information
如果period LEAD specifies is outside of the module's timescale, LEAD returns the value of the Substitute valueargument.
Calculation engine functionality differences
In Polaris, any number used for theOffset amountargument is rounded to the nearest integer. A value ofNaN(Not a Number) for theOffset amountargument returns theSubstitute valueargument.
In the Classic Engine, any number used for theOffset amountargument is rounded towards zero. A value ofNaNis equivalent to 0.
In the Classic Engine, you can use the LEAD function only with a time dimension. In Polaris, you can use LEAD with any dimension except Versions.
Examples
Example 1
LEAD(Value to offset, 2, Substitute value)
In this example, theLEAD 2line item returns line item from two periods after each cell. If two periods after a cell is outside of the module’s time range, the formula returns theSubstitute value．The formula returns the values of 500 and 600 contained in theMayandJunecolumns for theSubstitute value．The function does not contain theNon-positive behaviorargument, so the default behavior isNONSTRICT．
| Jan | Feb | March | April | May | June | |
| Value to offset |
1 |
2 |
3 |
4 |
5 |
6 |
| Substitute value |
100 | 200 | 300 | 400 | 500 | 600 |
| LEAD 2 | 3 | 4 | 5 | 6 | 500 | 600 |
Example 2
LEAD(Value to offset, Offset amount, Substitute value, [non-positive behavior])In this example, theLEAD 3line item returns theSubstitute valuefor the period theOffset amount指定。如果Substitute valuespecifies a period outside of the module's time range, the formula returns theSubstitute value．这意味着公式returns the value of 600 contained in theJunecolumn for theSubstitute value．The function does not contain theNon-positive behaviorargument, so the default behavior isNONSTRICT．
| 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 |
| LEAD 3 | 1 | 1 | 3 | 5 | 5 | 600 |
Example 3
LEAD(Value to offset, Offset amount, Substitute value, [, Non-positive behavior])
In this example, you can see how the different keywords for theNon-positive behaviorchange 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 |
LEADSEMISTRICT |
1 |
200 |
3 |
5 |
5 |
600 |
LEADSTRICT |
100 |
200 |
300 |
5 |
500 |
600 |
LEADNONSTRICT |
1 | 1 | 3 | 5 | 5 | 600 |
Disclaimer
We update Anapedia content regularly to provide the most up-to-date instructions.