Skip Headers
Oracle® OLAP Expression Syntax Reference
Release 11.2

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

LAG

LAG returns the value from an earlier time period.

Return Value

The same data type as the value expression

Syntax

LAG (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.

Example

This example returns the value from the prior year for each period.

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

Time Units Last Year
Q1.05 143607 146529
Q2.05 138096 143070
Q3.05 138953 148292
Q4.05 145062 149528
Q1.06 146819 143607
Q2.06 145233 138096
Q3.06 143572 138953
Q4.06 149305 145062

Related Topics

LAG_VARIANCE, LAG_VARIANCE_PERCENT, LEAD