The LAG function returns the values of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension. Typically, you use the LAG function to retrieve values for a previous time period.
The data type of the variable argument or NA
when you try to lag prior to the first period of a time dimension.
LAG(variable n, dimension, [STATUS|NOSTATUS|limit-clause])
A variable or expression that is dimensioned by dimension.
The offset (that is, the number of dimension values) to lag. LAG uses this value to determine the number of values that LAG should go back in dimension to retrieve the value of variable.
Typically, n is a positive INTEGER
that indicates the number of time periods (or dimension values) before the current one. When you specify a negative value for n, it indicates the number of time periods after the current o ne. In other words, using a negative value for n turns LAG into a LEAD function.
Note:
When using LAG in a model, see "Ensuring One-Way Dimensional Dependence" for information on how to code a value forn
so that Oracle OLAP does not use simultaneous blocks when solving the model.The dimension along which the lag 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 LAG to use that dimension, you can omit the dimension argument.
Specifies that LAG should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the lag.
(Default) Specifies that LAG should use the default status (that is, a list all the dimension values in their original order) when computing the lag.
Specifies that LAG should use the default status limited by limit-clause when computing the lag. You can use any valid LIMIT clause (see the entry for the LIMIT command for further information). To specify that LAG should use the current status limited by limit-clause when computing the lag, specify a LIMIT function for limit-clause.
Assigning Results to a Time-Series Variable
Use care when assigning the results of LAG back into the time-series variable. Results are assigned one cell at a time, so you can overwrite the whole array with the first value returned, instead of moving all the values over n positions. You can, however, use LAG to calculate a series of values based on the initial value.
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 has as values 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 2000 with sales for the corresponding months of 1999. You can use the LAG function to produce the values from 1999 in the same report with the 2000 values. The following statements
LIMIT product TO 'racquets' LIMIT district TO 'Dallas' LIMIT time TO 'Jan2000' 'Feb2000'- REPORT DOWN time sales HEADING 'Last Year' LAG(sales, 12, time, - LEVELREL timelevelrel)
produce this report.
DISTRICT: DALLAS -------PRODUCT------- ------RACQUETS------- TIME SALES Last Year -------------- ---------- ---------- Jan2000 125,879.86 118,686.75 Feb2000 150,833.64 142,305.99