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