Lag, Lead
Use these functions in an equation to point to different cells of a symbol based on a shift (forwards or backwards) in the dimension specified in the By statement.
You may find these functions useful when calculating cash flows.
Unlike most other functions, the Lag and Lead functions do not use the actual function word within syntax. Instead, Lag and Lead are denoted by negative values (with a minus sign) or positive values (no sign), respectively, enclosed in parentheses.
The order in which the values are selected depends on their priority:
Value | Description |
|---|---|
Positive shift values | Use to point to cells of a symbol appearing to the right (in the lead) of the current calculation cell in the dimension specified in a By statement. Values to the right have a higher priority number. |
Negative shift values | Use to point to cells of a symbol appearing to the left (lagging behind) of the current calculation cell in the dimension specified in a By statement. Values to the left have a lower priority number. |
Use to point to cells of a symbol appearing to the left (lagging behind) of the current calculation cell in the dimension specified in a By statement. Values to the left have a lower priority number.
The Lag and Lead functions are determined by the By and CalculationBlock functions. For example, if you use a CalculationBlock Rows statement followed with a By Columns statement, your system applies the Lag and Lead in the columns dimension.
For Lag statements to work properly, specify the priorities of the symbols. For example, if you have 12 months rolling up into four quarters and one year with the following priorities:
Symbol | Time period | Priority | ||||
|---|---|---|---|---|---|---|
Yr |
|
| Total Year | 1 | ||
| Q1 |
| First Quarter | 1 | ||
|
| P1 | January | 1 | ||
|
| P2 | February | 2 | ||
|
| P3 | March | 3 | ||
| Q2 |
| Second Quarter | 2 | ||
|
| P4 | April | 4 | ||
|
| P5 | May | 5 | ||
|
| P6 | June | 6 | ||
| Q3 |
| Third Quarter | 3 | ||
|
| P7 | July | 7 | ||
|
| P8 | August | 8 | ||
|
| P9 | September | 9 | ||
| Q4 |
| Fourth Quarter | 4 | ||
|
| P10 | October | 10 | ||
|
| P11 | November | 11 | ||
|
| P12 | December | 12 | ||
When priorities are specified this way:
- Yr ### will refer to P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, and P12.
- Yr ##1 will refer to Q1, Q2, Q3, and Q4.
- Yr #99 will refer to P1, P2, P3, Q1, P4, P5, P6, Q2, P7, P8, P9, Q3, P10, P11, P12, Q4, and Yr. This would give you unexpected results for a Lag or Lead calculation.
Note: The Lag and Lead functions can be used successfully only if symbols specified in the CalculationBlock are on the same level relative to their parent. In the examples above, Yr### and Yr##1 are valid but Yr#99 is not.
You cannot use Lag and Lead inside other functions such as Sum, Avg, and so on—they are meant to be used in equations only.
where:
- TargetSymName is a symbol that will contain the result of the calculation.
- SymName is a symbol in the dimension specified in a CalculationBlock statement.
- Shift is a number of cells away from the current calculation cell in the dimension specified in a By statement. Use Shift to select the value to be used in calculations.
If you use a Lag of (1), the first symbol in the By dimension is not calculated—it becomes the Lag reference for the second symbol. Similarly, if you use a Lead of (3), the last three symbols in the By dimension are not calculated—they become the Lead reference for other symbols.
See also