TM1: Leaf or N Level Dimension (Period N or Leaf only Time Dimension)

TM1 Leaf or N Level Dimension (Period N or Leaf only Time Dimension)

Many times when developing reports or running a TI process, I have come across the need to get a list of the previous or next n number of months. This is typically obtained by using the LastPeriods MDX statement.

When using the usual continuous period or date dimension, this can sometimes be a challenge when you start to cross over into different consolidations. Inconsistent results can arise when you start using the dimension index depending on how your dime nsion was constructed initially.

Using the example below, when getting a list of the last 6 months from 2012-09 in a continuous time dimension, the result can sometimes be incorrect as shown in Figure 1.

Figure 1

TM1 leaf 1

A simple and clean way of solving this issue is to create a separate Dimension N (Period N in this example), which contains only leaf level elements ordered correctly. This dimension can then be called upon and substituted in whenever there is a need to get the previous or next n number of months.

Using the same example as described above, but substituting in the leaf only dimension would give a correct result as shown in Figure 2.

 

Figure 2

TM1 leaf 2

Related Articles

QMetrix is ISO/IEC 27001 certified: Information Security Management System

QMetrix is pleased to announce we are now ISO/IEC 27001 certified, demonstrating our commitment to information and data security. Highlighted by various data breaches in recent times, cyber threats and criminal activities are on the rise. It is paramount for organisations to uphold high security standards and manage cyber-risks even before they occur in order

QMetrix is named 2024 APJ Workday Adaptive Planning Partner of the Year

QMetrix is named Winner of the 2024 Adaptive Planning Partner of the Year Award – APJ at the annual Workday Sales Kickoff. QMetrix announced it was named a winner of the 2024 Adaptive Planning Partner  of the Year Award – APJ by Workday, Inc. (NASDAQ: WDAY), a leading provider of solutions to help organisations manage

Stay in touch

Get the latest insights from QMetrix straight in you inbox.

Scroll to Top

This website uses cookies to ensure you get the best experience on our website. More Information

Skip to content