Working with Time Related MDX Functions
This article covers various MDX functions that allow you to work with time related dimensions.
These functions are not strictly for time related dimensions as they work on any dimension. However, using time related dimensions makes it a bit easier to explain and the examples more relevant.
For these examples I have a Period dimension that has two rollups in the Default hierarchy. One that simply aggregates Months into Years and then into Total Years. The other aggregates Months into Quarters then Halves then Full Years and then an overall total.
The }HierarchyProperties have been configured and relate to the second rollup i.e. Total Years by Quarters.
Years rollup in the Period Dimension
Halves and Quarters rollup within the Period dimension
Levels configuration in }HierarchyProperties
Ideally you would want to create additional named hierarchies if you were going to use multiple named levels or wanted to use some of the MDX below without using the Member Unique Name (MUN) to fully qualify the member. I have specifically created the rollups with Total Years by Quarter and linked the named levels in order to show how to reference the members in the MDX examples.
With the scene set, onto some functions and examples.
OpeningPeriod
Returns the first sibling from the descendants of a specified level.
A specified member can be optionally specified as a starting point. TM1 seems to require the member expression to resolve the expression.
Syntax:
OpeningPeriod( Level [ , Member ])
In the above syntax Level and Member relate to specified Levels, Members or an expression that returns a Level or Member.
Examples:
Example 1:
{OpeningPeriod([Period].[Period].[Leaves], [Period].[Period].[2021-FY])}
Returns:
2021/01
This is the first member at the Leaves level in the 2021-FY rollup.
Example 2:
{OpeningPeriod([Period].[Period].[Quarters], [Period].[Period].[2021-FY])}
Returns:
2021-Q1
This is the first member at the Quarters level in the 2021-FY rollup.
Example 3:
{OpeningPeriod([Period].[Period].[Leaves],[Period].[Period].[2021-H2])}
Returns:
2021/07
This is the first member at the Leaves level starting at the 2021-H2 member
Related functions:
ClosingPeriod, FirstChild
ClosingPeriod
Returns the last sibling from the descendants of a specified level.
A specified member can be optionally specified as a starting point. TM1 seems to require the member expression to resolve the expression.
Syntax:
ClosingPeriod( Level [ , Member ])
Examples:
Example 1:
{ClosingPeriod([Period].[Period].[Leaves],[Period].[Period].[2021-H2])}
Returns:
2021/12
This is the last Leaves level member in the 2021-H2 rollup.
Example 2:
{ClosingPeriod([Period].[Period].[Quarters],[Period].[Period].[2021-H2])}
Returns:
2021-Q4
2021-Q4 was returned being the last member in the Quarters level in the 2021-H2 rollup.
Related functions:
OpeningPeriod, LastChild
PeriodsToDate
Returns a set of sibling members from the same level as the specified member.
Siblings are related to the specified level in the dimension.
Syntax:
PeriodsToDate( Level [ ,Member ])
Examples:
Example 1:
PeriodsToDate([Period].[Period].[Quarters],[Period].[Period].[2021-Q3^2021/08])
Returns:
2021/07; 2021/08
2021/08 belongs to a Quarters level called Quarter 3. The siblings within Quarter 3 are 2021/07; 2021/08; 2021/09. It follow then that PeriodsToDate for 2021/08 within Quarter 3 would return 2021/07 and 2021/08.
Example 2:
PeriodsToDate([Period].[Period].[Halves],[Period].[Period].[2021-Q4^2021/10])
Returns:
2021/07; 2021/08; 2021/09; 2021/10
2021/10 belongs to 2021-Q4 which is a child of 2021-H2, a Halves level member. Leaf members under 2021-H2 are 2012/07;…;2012/12. The four members returned are thus the expected members.
Note:
You need to be specific about the Levels being used in parameter 1 and use the MUN in parameter 2 to specify the element in that level where multiple rollups exist in the same hierarchy.
Related functions:
MTD, YTD but these do not appear to be supported in TM1 currently.
LastPeriods
Returns a set of members based on a specified count and includes the specified member.
Syntax:
LastPeriods(Count [ ,Member ] )
Count can be positive or negative. Positive values will return predecessors or members that lag the current member.
Examples:
Example 1:
LastPeriods(4 ,[Period].[Period].[2021/08])
Returns:
2021/05; 2021/06; 2021/07; 2021/08
Related functions:
Lag
Lead
Returns a member, a specified number of positions after the specified member on the same level. Only one member is returned, not a set.
Syntax:
Member.Lead(Count)
Examples:
Example 1:
{[Period].[Period].[2021/05].LEAD(13)}
Returns:
2022/06
Returns 2022/06 which is 13 periods hence and on the same level as 2021/05
Example 2:
{[Period].[Period].[2021-Q1].LEAD(5)}
Returns:
2022-Q2
Five quarters from 2021-Q1 is 2022-Q2.
Related functions:
Lag, PeriodsToDate
Lag
Returns a preceding member from the same level as the specified member but based on a specified number of positions. Only one member is returned, not a set.
Syntax:
Member.Lag(Count)
Examples:
Example 1:
{[Period].[Period].[2021/09].LAG(5)}
Returns:
2021/04
Example 2:
{[Period].[Period].[2022-Q2^2022/04].LAG(5)}
Returns:
2021/11
Returns 2021/11 which is 5 periods before 2022/04 on the same level. C Levels between the members are ignored
Related functions:
Lead, LastPeriods
FirstChild
Returns the first child of the specified member.
Syntax:
Member.FirstChild
Examples:
Example 1:
{[Period].[Period].[2021-Q2].FirstChild}
Returns:
2021/04
Example 2:
{[Period].[Period].[2021-FY].FirstChild}
Returns:
2021-H1
Related functions:
LastChild, FirstSibling
LastChild
Returns the last child of the specified member.
Syntax:
Member.LastChild
Examples:
Example 1:
{[Period].[Period].[2021].LastChild}
Returns:
2021/12
Example 2:
{[Period].[Period].[2021-FY].LastChild}
Returns:
2021-H2
Related functions:
FirstChild, LastSibling
FirstSibling
Returns the first sibling of the specified member from the same level.
Syntax:
Member.FirstSibling
Examples:
Example 1:
{[Period].[Period].[2021/05].FirstSibling}
Returns:
2021/01
First leaf level member in the 2021 rollup.
Example 2:
{[Period].[Period].[2021-H2^2021-Q4].FirstSibling}
Returns:
2021-Q3
First quarter in the 2021-H2 rollup.
Related functions:
LastSibling, FirstChild
LastSibling
Returns the last sibling of the specified member from the same level.
Syntax:
Member.LastSibling
Examples:
Example 1:
{[Period].[Period].[2021/05].LastSibling}
Returns:
2021/12
Last leaf level member in the 2021 rollup.
Example 2:
{[Period].[Period].[2021-H1^2021-Q1].LastSibling}
Returns:
2021-Q2
Last quarter in the 2021-H1 rollup.
Related functions:
FirstSibling, LastChild
NextMember
Returns the next member at the same level for the specified member.
Syntax:
Member.NextMember
Examples:
Example 1:
{[Period].[Period].[2021/04].NextMember}
Returns:
2021/05
Example 2:
{[Period].[Period].[2021-Q4].NextMember}
Returns:
2022-Q1
Looking at only quarters in the Period dimension, 2022-Q1 is the next member.
Related functions:
PrevMember, Lead
PrevMember
Returns the previous member at the same level for the specified member.
Syntax:
Member.PrevMember
Examples:
Example 1:
{[Period].[Period].[2021/04].PrevMember}
Returns:
2021/03
Example 2:
{[Period].[Period].[2022-Q1].PrevMember}
Returns:
2021-Q4
Looking at only quarters in the Period dimension, 2021-Q4 is the previous member.
Related functions:
NextMember, Lag
Cousin
Returns the child member with the same position relative to the parent/ancestor member for the specified child member.
Syntax:
Cousin( Member , Ancestor_Member )
Examples:
Example 1:
{Cousin([Period].[Period].[2021/06], [Period].[Period].[2022])}
Returns:
2022/06
2021/06 is the sixth child of 2021 therefore the sixth child of 2022 is returned i.e. 2022/06
Related functions:
Lead, Lag
As noted in the introduction, these functions are not limited to Time dimensions and could be used on any dimension but the results may not be applicable.
Returning the first child of Region B in relation to the first child in Region A may return a member but may be arbitrary. In other cases where you potentially have ordered items, the returned member may well be useful.
The above is not an exhaustive list of functions that could be used with Time dimensions but has hopefully exposed you to those that may be useful in your MDX writing.
As always, please let me know should you find any errors or have questions related to the content.
By George Tonkin, Business Partner at MCi.
Further Reading
Part 1 – An Introduction to MDX Views
Part 2 – Using Calculated Members in MDX
Part 3 – Aggregate Calculated Members in MDX
Part 4 – Using Calculated Members to Add Information
MDX Query Fundamentals
TM1/Planning Analytics – MDX Reference Guide
Working with Time Related MDX Functions
Discovering MDX Intrinsic Members
One Response