MDX - 'NextPeriods'

mdx

#1

For a report I needed the user to select a calendar week and then showing the next 8 weeks in the columns.
I found that there is a MDX function ‘LastPeriods’ with the following syntax:

LastPeriods ( Index, [ Member_Expression ] )

Arguments

Index
A valid numeric expression that specifies a number of periods.

Member_Expression
A valid Multidimensional Expressions (MDX) expression that returns a member.

But there is no equivalent for the next periods. I found a way to do it like this:
( Calendar Week = dimension name / {{week}} = ng-model of the users selection)

{ LastPeriods ( 8, [Calendar Week].[{{week}}].NextMember.NextMember.NextMember.NextMember.NextMember.NextMember.NextMember.NextMember ) }


#2

Hi JD

Did you try a negative index to LastPeriods? Works for me!

The other thing we are doing a lot is to parse from/to selections in the page into MDX using simple {[timeDim].[fromPeriod]:[timeDim].[toPeriod]}

The main thing to watch out for is that both LastPeriods and : MDX operator are extremely sensitive to dimension leveling and indexing. So really don’t play well with TM1 time dimensions having lots of alternate rollups. In the “new world” of PA with proper alternate hierarchies this will cease to be a problem but it isn’t the world we are currently in so may require building one or more helper dimensions with sinle rollup or flat to generate the list of elements and then parsing that back in.


#3

Thanks Scott,

Didn’t know about the minus index, also a great -even better- way to do it!