LAG_VARIANCE_PERCENT

LAG_VARIANCE_PERCENT returns the percent difference between values for the current time period and an earlier period.

Return Value

The same data type as the value expression

Syntax

LAG_VARIANCE_PERCENT (lag_args) OVER (lag_clause)

lag_args::=

expression, offset [, {default_expression | CLOSEST} ]

lag_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 back 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 back.

dimension_id

The Time dimension over which the lag is calculated.

hierarchy_id

The hierarchy over which the lag 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 before the current member. (Default)
`PARENT`	The member at the same level with the same parent `offset` periods before the current member.
`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.
`ANCESTOR`	The period at the same level as the current period and whose ancestor is `offset` positions before the ancestor of the current period.

Examples

This example returns the percent difference in value between the current period and the equivalent period in the prior year.

LAG_VARIANCE_PERCENT (GLOBAL.UNITS_CUBE.UNITS, 1) OVER (HIERARCHY GLOBAL.TIME.CALENDAR ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_YEAR)

Time	Units	Last Year	Difference	Percent
Q1.05	143607	146529	-2922	-.02
Q2.05	138096	143070	-4974	-.03
Q3.05	138953	148292	-9339	-.06
Q4.05	145062	149528	-4466	-.03
Q1.06	146819	143607	3212	.02
Q2.06	145233	138096	7137	.05
Q3.06	143572	138953	4619	.03
Q4.06	149305	145062	4243	.03