Skip Headers
Oracle® OLAP Expression Syntax Reference
Release 11.2

Part Number E23381-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

LEAD_VARIANCE

LEAD_VARIANCE returns the difference between values for the current time period and the offset period.

Return Value

The same data type as the value expression

Syntax

LEAD_VARIANCE (lead_args) OVER (lead_clause)

lead_args::=

expression, offset [, {default_expression | CLOSEST} ]

lead_clause::=

[ {DIMENSION dimension_id | HIERARCHY hierarchy_id} ]
[ [BY] { LEVEL 
       | PARENT 
       | GREGORIAN {YEAR | QUARTER | MONTH | WEEK | DAY}
       | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                      | HIERARCHY LEVEL hier_level_id
                      }
       }
                [POSITION FROM {BEGINNING | END}] 
] 

Arguments

expression

A dimensional expression whose values you want to calculate.

offset

A numeric expression for the number of periods to count forward from the current time period.

default_expression

The value returned when offset does not identify a valid period. This clause is either an expression of any data type or the CLOSEST keyword for the closest match. The closest match is the first member when counting forward.

dimension_id

The Time dimension over which the lead is calculated.

hierarchy_id

The hierarchy over which the lead is calculated. Otherwise, the default hierarchy for dimension_id is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

BY subclause

The BY subclause identifies the range of time periods used when counting the offset. Following are descriptions of the keywords:

Offset Unit Description
LEVEL The member at the same level offset periods after the current member. (Default)
PARENT The member at the same level with the same parent offset periods after the current member.
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 period.
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 The period at the same level as the current period and whose ancestor is offset positions after the ancestor of the current period.

Examples

This calculation returns the difference between the current value and the value of the next time period based on calendar quarter. The sample output from this calculation appears in the Difference column.

LEAD_VARIANCE (GLOBAL.UNITS_CUBE.UNITS, 1, CLOSEST) OVER (DIMENSION GLOBAL.TIME BY ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_QUARTER)

This calculation generates the data in the Percent column.

Time Parent Units Next Qtr Difference
2005 TOTAL 565718 -- --
Q1-05 CY2005 143607 138096 5511
Q2-05 CY2005 138096 138953 -857
Q3-05 CY2005 138953 145062 -6109
Q4-05 CY2005 145062 146819 -1757
Jan-05 CY2005.Q1 50098 40223 9875
Feb-05 CY2005.Q1 43990 45477 -1487
Mar-05 CY2005.Q1 49519 52396 -2877
Apr-05 CY2005.Q2 40223 45595 -5372
May-05 CY2005.Q2 45477 46882 -1405
Jun-05 CY2005.Q2 52396 46476 5920
Jul-05 CY2005.Q3 45595 47476 -1881
Aug-05 CY2005.Q3 46882 47496 -614
Sep-05 CY2005.Q3 46476 50090 -3614
Oct-05 CY2005.Q4 47476 47776 -300
Nov-05 CY2005.Q4 47496 47695 -199
Dec-05 CY2005.Q4 50090 51348 -1258

Related Topics

LAG, LEAD, LEAD_VARIANCE_PERCENT