LEAD returns a value from a period in the future.
You could use LEAD to calculate how this month's earnings compare to next year's projected monthly earnings.
Syntax
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 as Value to offset | Value to return if the Offset 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 LEAD function uses the Substitute value argument. The keywords are NONSTRICT, SEMISTRICT, and STRICT. There's more information in the Non-positive behavior keywords section below. |
The LEAD function returns a result of the same data type as the Value to offset argument.
This function allows for three arguments. If you have these functions with two arguments and don't declare a third one, this will default to Time. If you declare a third argument, then you can use any related dimension to the line item as an argument.
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. 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. |
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.
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 LEAD function only with a time dimension. In Polaris, you can use LEAD with any dimension except Versions.
Syntax example
LEAD(Value to offset, 2, 0)
In this example, the LEAD 1 line 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 the Substitute value of 0, as seen in the June column. The function does not contain the Non-positive behavior argument, so the default behavior is NONSTRICT.
| 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
If the period LEAD specifies is outside of the module's timescale, LEAD returns the value of the Substitute value argument.
Examples
Example 1
LEAD(Value to offset, 2, Substitute value)
In this example, the LEAD 2 line 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 the Substitute value. The formula returns the values of 500 and 600 contained in the May and June columns for the Substitute value. The function does not contain the Non-positive behavior argument, so the default behavior is NONSTRICT.
| 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, the LEAD 3 line item returns the Substitute value for the period the Offset amount specifies. If the Substitute value specifies a period outside of the module's time range, the formula returns the Substitute value. This means the formula returns the value of 600 contained in the June column for the Substitute value. The function does not contain the Non-positive behavior argument, so the default behavior is NONSTRICT.
| 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 the Non-positive behavior 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 |
LEADSEMISTRICT | 1 | 200 | 3 | 5 | 5 | 600 |
LEADSTRICT | 100 | 200 | 300 | 5 | 500 | 600 |
LEADNONSTRICT | 1 | 1 | 3 | 5 | 5 | 600 |
