Lag

Using the order of members existing in an Essbase database outline, the MDX Lag function returns a member that is n steps behind a given member, along the same generation or level (as defined by layertype).

Syntax

member.Lag (index [,layertype ] [, hierarchy ] )

Lag ( member, index [, hierarchy ] )

Parameters

member: The starting member from which .LAG counts to a given number of previous members.
index: A number n representing how many steps prior to <member> to count.
layertype: GENERATION or LEVEL. Generation is the default.
hierarchy: Optional. A specific hierarchy within the time dimension.

Notes

If the member specified by the Lag function does not exist, the result is an empty member. For example, using Sample Basic, [Jun].lag (12) returns an empty member.
When multiple hierarchies are enabled, this function returns NULL when the source member is in one hierarchy and the result member belongs to a different hierarchy.

Example

The following expression:

[Jun].lag (3)

returns the member that is 3 steps prior to Jun:

[Mar]

The following expression:

[Jun].lag (-3)

returns the member that is 3 steps following Jun:

[Sep]

For every month, the following query displays the sales and average over the last three months.

WITH MEMBER
 [Measures].[Average Sales in Last 3 months]
AS
'Avg(
     {[Year].CurrentMember,
      [Year].CurrentMember.Lag(1),
      [Year]. CurrentMember.Lag(2)
     }, 
     [Measures].[Sales]
 )'
SELECT 
 {[Measures].[Sales], 
  [Measures].[Average Sales in Last 3 months] 
 } 
ON COLUMNS,
 [Year].Levels(0).Members
ON ROWS
FROM Sample.Basic

This query returns the grid:

Table 4-102 Output Grid from MDX Example

(axis)	Sales	Average Sales in Last 3 Months
Jan	31538	31538
Feb	23069	31803.500
March	32213	31940
April	32917	32399.667
May	33674	32934.667
Jun	35088	33893
Jul	36134	34965.333
Aug	36008	35743.333
Sep	33073	35071.667
Oct	32828	33969.667
Nov	31971	32624
Dec	33342	32713.667