The LAG function returns the values of a dimensioned variable or expression at a specified offset of a dimension before 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 before the first period of a time dimension.
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.
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.
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 the following definitions in your analytic workspace.
DEFINE time DIMENSION TEXT DEFINE timelevels DIMENSION TEXT DEFINE timelevelrel RELATION timelevels <time> DEFINE product DIMENSION TEXT DEFINE district DIMENSION TEXT DEFINE sales VARIABLE DECIMAL <time product district>
Assume also that those object have the values shown in the following reports.
REPORT timelevelrel TIME TIMELEVELREL -------------- ---------- AllYears AllYears 2004 Years 2005 Years 2006 Years Jan2005 Months Feb2005 Months Mar2005 Months Apr2005 Months ... ... Oct2006 Months Nov2006 Months Dec2006 Months
REPORT product PRODUCT -------------- TVs DVDs Computers REPORT district DISTRICT -------------- All Districts Mass Conn Boston Springfield Hartford New Haven
Now assume that you issue the following LIMIT statements to limit product, district, and time.
LIMIT product TO 'TVs' LIMIT district TO 'Hartford' LIMIT time TO 'Jan2006' 'Feb2006' 'Mar2006' 'Apr2006' 'May2006' 'Jun2006' 'Jul2006' 'Aug2006' 'Sep2006' 'Oct2006' 'Nov2006' 'Dec2006'
You can issue a REPORT statement with LAG to show the value of sales for months in both 2006 and for 2005.
REPORT DOWN time HEADING 'Sales 2006' sales HEADING 'Sales 2005' LAG(sales, 12, time, RELATION timelevelrel)
DISTRICT: Hartford
-------PRODUCT-------
---------TVs---------
TIME Sales 2006 Sales 2005
-------------- ---------- ----------
Jan2006 1,542.91 1,627.51
Feb2006 1,786.07 1,100.13
Mar2006 1,794.43 1,667.61
Apr2006 1,942.92 1,346.66
May2006 1,530.08 1,509.51
Jun2006 1,613.60 1,242.47
Jul2006 1,666.35 1,307.17
Aug2006 1,413.79 1,033.93
Sep2006 1,526.98 1,773.96
Oct2006 1,112.85 1,103.78
Nov2006 1,193.41 1,132.39
Dec2006 1,851.19 1,543.62