LEAD returns the value of an expression for a later time period.
LEAD_VARIANCE returns the difference between values for the current time period and the offset period.
LEAD_VARIANCE_PERCENT returns the percent different between values for the current time period and the offset period.
The same data type as the value expression
{LEAD | LEAD_VARIANCE | LEAD_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 time periods to count forward from the current period.
default is the value returned when offset does not identify a valid time period. This clause is either an expression of any data type or the keyword CLOSEST for the closest match. The closest match is the last time period when counting forward.
dimension is the Time dimension over which the lead is calculated.
hierarchy is the hierarchy over which the lead 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 after the start date of the current period.
GREGORIAN QUARTER: The period at the same level with a start date exactly offset quarters after the start date of the current period.
GREGORIAN MONTH: The period at the same level with a start date exactly offset months after the start date of the current member.
GREGORIAN WEEK: The period at the same level with a start date exactly offset weeks after the start date of the current period.
GREGORIAN DAY: The period at the same level with a start date exactly offset days after the start date of the current period.
ANCESTOR AT LEVEL level [ POSITION FROM END ]: The period at the same level as the current member and whose ancestor is offset positions after the ancestor of the current period. The function returns the value of the period in the same position as the current period, for example, the second from the top. When POSITION FROM END is used, the function starts at the end, for example, the third dimension member from the end.
LEAD (GLOBAL.UNITS_CUBE.UNITS, 1 CLOSEST) OVER HIERARCHY (GLOBAL.TIME BY ANCESTOR AT LEVEL GLOBAL.TIME.QUARTER)
LEAD (GLOBAL.UNITS_CUBE.UNITS, 1 CLOSEST) OVER HIERARCHY (GLOBAL.TIME BY ANCESTOR AT LEVEL GLOBAL.TIME.YEAR)
LEAD (GLOBAL.UNITS_CUBE.UNITS, 1 CLOSEST) OVER HIERARCHY (GLOBAL.TIME BY ANCESTOR AT LEVEL GLOBAL.TIME.YEAR POSITION FROM END)
LEAD_VARIANCE (GLOBAL.UNITS_CUBE.UNITS, 1 CLOSEST) OVER HIERARCHY (GLOBAL.TIME BY ANCESTOR AT LEVEL GLOBAL.TIME.QUARTER)
LEAD_VARIANCE_PERCENT (GLOBAL.UNITS_CUBE.UNITS, 1 CLOSEST) OVER HIERARCHY (GLOBAL.TIME BY ANCESTOR AT LEVEL GLOBAL.TIME.QUARTER)
Time |
Units |
Lead 1 |
Lead 2 |
Lead 3 |
Lead Variance |
Lead Variance Percent |
|---|---|---|---|---|---|---|
2002 |
395,319.52 |
null |
394,448.64 |
394,448.64 |
null |
null |
Q1-02 |
98,663.63 |
98,747.38 |
98,761.70 |
98,761.70 |
0.00 |
-83.75 |
Q2-02 |
98,747.38 |
99,243.90 |
98,476.02 |
98,476.02 |
-0.01 |
-496.52 |
Q3-02 |
99,243.90 |
98,664.61 |
98,693.98 |
98,693.98 |
0.01 |
579.29 |
Q4-02 |
98,664.61 |
98,761.70 |
98,516.93 |
98,516.93 |
0.00 |
-97.09 |
Jan-02 |
33,005.36 |
32,758.61 |
32,965.29 |
32,965.29 |
0.01 |
246.76 |
Feb-02 |
32,820.50 |
33,083.42 |
32,913.85 |
32,913.85 |
-0.01 |
-262.92 |
Mar-02 |
32,837.77 |
32,905.36 |
32,882.56 |
32,882.56 |
0.00 |
-67.59 |
Apr-02 |
32,758.61 |
33,211.92 |
32,857.57 |
32,857.57 |
-0.01 |
-453.31 |
May-02 |
33,083.42 |
32,918.05 |
32,784.84 |
32,784.84 |
0.01 |
165.36 |
Jun-02 |
32,905.36 |
33,113.92 |
32,833.62 |
32,833.62 |
-0.01 |
-208.57 |
Jul-02 |
33,211.92 |
32,882.09 |
32,930.44 |
32,930.44 |
0.01 |
329.83 |
Aug-02 |
32,918.05 |
32,725.96 |
32,779.45 |
32,779.45 |
0.01 |
192.09 |
Sep-02 |
33,113.92 |
33,056.56 |
32,984.09 |
32,984.09 |
0.00 |
57.36 |
Oct-02 |
32,882.09 |
32,965.29 |
32,975.62 |
32,975.62 |
0.00 |
-83.20 |
Nov-02 |
32,725.96 |
32,913.85 |
32,957.95 |
32,957.95 |
-0.01 |
-187.89 |
Dec-02 |
33,056.56 |
32,882.56 |
32,583.36 |
32,583.36 |
0.01 |
174.00 |
Copyright © 2003, 2007, Oracle. All rights reserved.