One of the many strengths of MDX is that there are many different functions you can use for travelling through time. And as a plus, none of them require you to sling-shot around the Sun at maximum Warp speed. In this post, we will introduce the simplest of these functions: LEAD() and LAG(). Although I am using these for Time, note that they will work with any ordered hierarchy. It does not HAVE to be Time related.
LEAD()
The LEAD() function takes two inputs. The first input is the expression to which you append .lead(N). The second is the integer value you place where I have the N in the parentheses, known as the index. The output is the member of the hierarchy at the same level as the member passed that is N members further in the sort order of that hierarchy. This will be easiest to show with a simple date example.
Suppose we find ourselves on January 10th, 2011, shown highlighted in Image 1.
Image 1
The MDX expression for this member of my Date Hierarchy would be:
[Date].[Date Hierarchy].[Full Date].[2011-01-10]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
If we want to move forward on the calendar by two days, we could append the LEAD() function with an index of 2. That expression would look like this:
[Date].[Date Hierarchy].[Full Date].[2011-01-10].lead(2)
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Our result will be the date shown highlighted in Image 2: January 12th, 2011. The resulting member expression would, therefore, be:
[Date].[Date Hierarchy].[Full Date].[2011-01-12]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Image 2
Since we passed a member at the [Full Date] level of the hierarchy, the LEAD() function knew we would be traversing members at that level. By passing a 2 as the index, we travelled 2 members further down the line according to the hierarchy’s configured sort order in the cube. Since this hierarchy is sorted by date, we travelled 2 [Full Date] members into the future.
My Borg cube awaits.
In Query 1, we start with my [Calendar Month] member of January of 1989, [1989-01]. When we apply the LEAD() function with an index of 3, the brilliant explanation above, in addition to your spectacular skills of predictive analytics, can likely let you determine that we will be travelling 3 calendar months into the future, arriving at April of 1989.
Query 1
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Date].[Date Hierarchy].[Calendar Month].[1989-01].lead(3) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 1
If you want to picture Leonard Nimoy as Spock calmly standing with his hands clasped behind his back raising an eyebrow and saying, “Bam,” you go right ahead. I just did and I’m still laughing. Excuse me, I need a moment.
<<pause>>
OK. I’m better, now.
You may be wondering what would happen if you you used a negative number as the index for the LEAD() function. That merely causes the function to go in the opposite direction. So, in Query 2, we merely change the 3 in the index to a –3.
Query 2
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Date].[Date Hierarchy].[Calendar Month].[1989-01].lead(-3) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 2
Instead of travelling 3 [Calendar Month] members forward, we travel 3 members backward. I’m totally still giggling about the Leonard Nimoy thing.
LAG()
The LAG() function also takes two inputs. As a matter of fact, the only real difference between LEAD() and LAG() is the direction they travel along ordered hierarchies. While LEAD() travels forward with positive index values, LAG() travels backward with positive index values. And just like LEAD(), passing negative index values to LAG() causes it to change direction.
So, we can start at January 12th, 2011, as in Image 3, and apply the LAG() function with an index of 2.
[Date].[Date Hierarchy].[Full Date].[2011-01-12].lag(2)
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Image 3
This takes us back 2 [Full Date] members along the hierarchy and we end up at the member for January 10th, 2011 as shown in Image 4.
[Date].[Date Hierarchy].[Full Date].[2011-01-10]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Image 4
To the Borg cube.
Query 3 starts at the [Calendar Month] member for January of 1989 and uses the LAG() function with an index of 1 to go back 1 month.
Query 3
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Date].[Date Hierarchy].[Calendar Month].[1989-01].lag(1) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 3
Travelling one month back from January of 1989 takes us back to December of 1988.
Just like we did with LEAD(), in Query 4 we pass a negative value as the index and travel in the opposite direction, this time going forward in time with the LAG() function.
Query 4
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Date].[Date Hierarchy].[Calendar Month].[1989-01].lag(-1) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 4
That takes us one [Calendar Month] member forward to February of 1989.
These two simple functions, LEAD() and LAG(), provide solid ways of navigating back and forth within the levels of ordered hierarchies. And because each one can travel in both directions, it allows for you to dynamically change direction as needed based on other criteria in the query. I urge you to play with these functions on your own.
Next time, we get a little more advanced with the ParallelPeriod() function.
Mark Vaillancourt 
Mark – thanks for creating this series of MDX articles. It’s really improved my knowledge of MDX querying, and I like the Star Trek references, too.
Cheers.
LikeLike
Thanks, Richard. Much appreciated. I had a great time putting it together.
LikeLike