LAG returns the value of an expression for an earlier time period.
LAG_VARIANCE returns the difference between values for the current time period and an earlier period.
LAG_VARIANCE_PERCENT returns the percent different between values for the current time period and an earlier period.
The same data type as the value expression
{LAG | LAG_VARIANCE | LAG_VARIANCE_PERCENT}
(value_expr, offset, [, default])
OVER HIERARCHY ({dimension | hierarchy} [[BY] offset_unit])
value_expr is a dimensional expression whose values you want to calculate.
offset is a numeric expression for the number of periods to count back from the current time period.
default is the value returned when offset does not identify a valid period. This clause is either an expression of any data type or the keyword CLOSEST for the closest match. The closest match is the first member when counting back.
dimension is the Time dimension over which the lag is calculated.
hierarchy is the hierarchy over which the lag is calculated. Otherwise, the default hierarchy for dimension is used.
offset_unit can be any of the following keywords.
GREGORIAN YEAR: The period at the same level with a start date exactly offset years before the start date of the current period.
GREGORIAN QUARTER: The period at the same level with a start date exactly offset quarters before the start date of the current period.
GREGORIAN MONTH: The period at the same level with a start date exactly offset months before the start date of the current period.
GREGORIAN WEEK: The period at the same level with a start date exactly offset weeks before the start date of the current period.
GREGORIAN DAY: The period at the same level with a start date exactly offset days before the start date of the current period.
MEMBER: The period offset positions before the current period in the same level.
ANCESTOR AT LEVEL level [ POSITION FROM END ]: The period at the same level as the current period and whose ancestor is offset positions before the ancestor of the current period. When POSITION FROM END is used, the function starts at the end, for example, the third dimension member from the endWhen POSITION FROM END is used, the function starts at the end, for example, the third dimension member from the end. .
The following example returns the value of the equivalent member in the previous quarter for the current member.
LAG (GLOBAL.UNITS_CUBE.UNITS, 1) OVER HIERARCHY (GLOBAL.TIME.CALENDAR ANCESTOR AT LEVEL GLOBAL.TIME.QUARTER)
Time |
Units |
Lag |
|---|---|---|
| 2003 | 394,448.64 |
NA |
| Q1-03 | 98,761.70 |
98,664.61 |
| Q2-03 | 98,476.02 |
98,761.70 |
| Q3-03 | 98,693.98 |
98,476.02 |
| Q4-03 | 98,516.93 |
98,693.98 |
| Jan-03 | 32,965.29 |
32,882.09 |
| Feb-03 | 32,913.85 |
32,725.96 |
| Mar-03 | 32,882.56 |
33,056.56 |
| Apr-03 | 32,857.57 |
32,965.29 |
| May-03 | 32,784.84 |
32,913.85 |
| Jun-03 | 32,833.62 |
32,882.56 |
| Jul-03 | 32,930.44 |
32,857.57 |
| Aug-03 | 32,779.45 |
32,784.84 |
| Sep-03 | 32,984.09 |
32,833.62 |
| Oct-03 | 32,975.62 |
32,930.44 |
| Nov-03 | 32,957.95 |
32,779.45 |
| Dec-03 | 32,583.36 |
32,984.09 |
Copyright © 2003, 2007, Oracle. All rights reserved.