LEAD

The LEAD function returns the values of a dimensioned variable or expression at a specified offset of a dimension after the current value of that dimension. Typically, you use the LEAD function to retrieve values for a future time period.

Return Value

The data type of the variable argument or NA when you try to retrieve a value from beyond the last period defined for the time dimension.

Syntax

LEAD(variable, n, [time-dimension], [[STATUS|NOSTATUS|limit-clause] )

Parameters

variable

A variable or expression that is dimensioned by dimension.

n

The offset (that is, the number of dimension values) to lead. LEAD uses this value to determine the number of values that LEAD should go ahead in dimension to retrieve the value of variable. To count the values, LEAD uses the default status, unless you use the STATUS keyword or the limit-clause argument to specify a different dimension status.

Normally, n is a positive INTEGER that indicates the number of time periods (or dimension values) after the current one. When you specify a negative value for n, it indicates the number of time periods before the current one. In effect, using a negative value for n turns LEAD into a LAG function.

Note:

When using LEAD in a model, see "Ensuring One-Way Dimensional Dependence" for information on how to code a value for n so that Oracle OLAP does not use simultaneous blocks when solving the model.

dimension

The dimension along which the lead occurs. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR.

When variable has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want LEAD to use that dimension, you can omit the dimension argument.

STATUS

Specifies that LEAD should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the lead.

NOSTATUS

Specifies that LEAD should use the default status (that is, a list all the dimension values in their original order) when computing the lead.

limit-clause

Specifies that LEAD should use the default status limited by limit-clause when computing the lead.

The syntax of limit-clause is the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

To specify that LAG should use the current status limited by limit-clause when computing the lag, specify a LIMIT function for limit-clause.

Examples

Example 8-6 Using LEAD

Assume that you have a sales variable that is dimensioned by three dimensions of the TEXT type (named product, district, and time). The time dimension is a hierarchical dimension with the following values.

1999
2000
Jan1999
Feb1999
...
Dec1999
Jan2000
Feb2000
...
Dec2000

Also, assume that there is a dimension named timelevels that contains the names of the levels of the time dimension (that is, Month and Year) and a relation named timelevelrel that is dimensioned by time and that has values from timelevels (that is, the related dimension of timelevelrel is timelevels). A report of timelevelrel shows these relationships.

TIME           TIMELEVELREL
-------------- ------------
1999           Year
2000           Year
Jan1999        Month
Feb1999        Month
...            ...
Dec1999        Month
Jan2000        Month
Feb2000        Month
...            ...
Dec2000        Month

Suppose you want to compare racquet sales in Dallas for the first two months of 1999 with sales for the corresponding months of 2000. You can use the LEAD function to produce the values from 2000 in the same report with the 1999 values. The following statements

LIMIT product TO 'Racquets'
LIMIT district TO 'Dallas'
LIMIT time TO 'JAN1999' 'FEB1999'
REPORT DOWN time sales HEADING 'Following Year' LEAD(sales, 12, time, time LEVELREL timelevelrel)

produce this report.

DISTRICT: DALLAS
               -------PRODUCT-------
               ------RACQUETS-------
TIME           SALES      Following Year
-------------- ---------- ---------------------
Jan2000        118,686.75 125,879.86
Feb2000        142,305.99 150,833.64