MDX expression for NextMember in case of variable source member

Post Reply
Wim Gielis
MVP
Posts: 2109
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

MDX expression for NextMember in case of variable source member

Post by Wim Gielis » Thu Feb 07, 2019 12:35 am

Hi,


[interesting topic, yet through own research and testing I answered my own questions. See last reply in this topic. Put differently, spoiler alert for the 3rd message in this thread :D ]

Note that:
This topic deals with the challenges in MDX of how to use certain functions based on dynamic inputs (like an element in another subset). I did not find this on the world wide web.

Caveat:
Some of the functions used below rely on the index order of dimension elements. It is important that this order is correct, if not, it can lead to strange and unexpected results. Some of the functions used are not officially supported in TM1 so experiment and test before you use them.


So here goes:

I was looking for an MDX way to find the next element in a dimension, if the base element is contained in a subset. Let me make illustrate this. I have a dimension of weeks:
01.png
01.png (13.58 KiB) Viewed 337 times
A subset called 'Current week' contains the... current week. Suppose we take Wk_06_2019:
02.png
02.png (10.72 KiB) Viewed 337 times
Question: what MDX expression can we use to have the next week ? ==> Wk_07_2019

I don't want to get into element names that are a number, add 1, then retrieve the week like that.
Nor do I want to use Turbo Integrator.
Nor is this input by the user in a parameter cube, or as attributes (though that would be preferred, but that's not the challenge)
Nor should we use rules for this.
My challenge is really in getting NextMember (and similar functions) operating on dynamic inputs rather than hardcoded elements.

Because I know that NextMember works fine for hardcoded elements:
03.png
03.png (11.11 KiB) Viewed 337 times
But that hardcoded thing in the middle should be the first (only) element of the subset 'Current week'. It's fine to have that subset name hardcoded in the MDX.

Functions like Lag, Lead, NextMember, PrevMember, FirstSibling, LastSibling, Siblings and LastPeriods are similar. Just mentioning them here in case anyone is using Google or the Search function.

Thanks,

Wim
Last edited by Wim Gielis on Thu Feb 07, 2019 9:58 am, edited 6 times in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Wim Gielis
MVP
Posts: 2109
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: MDX expression for NextMember in case of variable source member

Post by Wim Gielis » Thu Feb 07, 2019 12:43 am

Now, I have been banging my head against the wall on this one and I seem to have a solution:

Code: Select all

{StrToMember("[Week].[" + MemberToStr( TM1Member( [Week].[Current week].Item(0), [Week] )) + "]").NextMember}
doesn't look that pretty though. Moreover, it fails if the element name exists in ANY other dimension (or as an alias):

04.png
04.png (44.43 KiB) Viewed 333 times


Without the other dimension in the model, it works fine:
05.png
05.png (26.75 KiB) Viewed 333 times
3 notes:
- StrToMember and MemberToStr are not part of the list of MDX functions supported in TM1. This increases the need for a different (simpler) solution with supported functions.
- here we have weeks as an example but functions like LastPeriods, Lead/Lag, NextMember/PrevMember, also work on dimensions that do not have a time concept
- I can get LastPeriods to work in a more simple and elegant way, thanks to the bihints MDX primer:

Code: Select all

{ LastPeriods(1, TM1Member( [Week].[Current week].Item(0), 0) )}
but LastPeriods will always contain that current week. It cannot do an offset like Lead/Lag or NextMember/PrevMember.

Any insights please ?

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Wim Gielis
MVP
Posts: 2109
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: MDX expression for NextMember in case of variable source member

Post by Wim Gielis » Thu Feb 07, 2019 2:25 am

Got it ! I'm answering my own question ;-) but I learned new stuff today, and the 2 previous posts are interesting too.

I am now using the MDX function ParallelPeriod (which is supported).

Code: Select all

{ ParallelPeriod( [Week].[level002], -1, TM1Member( [Week].[Current week].Item(0), 0) )}
08.png
08.png (28.2 KiB) Viewed 317 times
The amibiguous character error of last post is solved here. You can have the same element names in several dimensions.

One last thing. If I want to use level names in Cognos BI, I would enter them in the }HierarchyProperties control cube.
Whenever you do this, you must run the TI command:

Code: Select all

RefreshMDXHierarchy( '' );
# or for only 1 dimension:
RefreshMDXHierarchy( 'Weeks' );
or restart TM1.

As such, this breaks the MDX expression and the resulting subset is empty. You need to use the level name that you enter for level002, instead of level002. Then it works fine.

09.png
09.png (65.54 KiB) Viewed 315 times

Happy TM1'ing !

Wim
Last edited by Wim Gielis on Thu Feb 07, 2019 9:38 am, edited 1 time in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

User avatar
Steve Rowe
Site Admin
Posts: 1933
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: MDX expression for NextMember in case of variable source member

Post by Steve Rowe » Thu Feb 07, 2019 9:25 am

Interesting work Wim but...

To the OP you should take care building critical functionality that depends on the ordering of the dimension as it is very hard to have direct control over it.

This would be much simpler if you just had an attribute that held the next week.

Sorry Wim, just realised the you were the OP! teaching a granny to suck eggs! Apologies...

Wim Gielis
MVP
Posts: 2109
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: MDX expression for NextMember in case of variable source member

Post by Wim Gielis » Thu Feb 07, 2019 9:31 am

Agreed Steve. Your comments are valuable no matter how uses these functions.

I think that it was more a challenge on how to use certain functions based on dynamic inputs (element in another subset). Until now I couldn't do a number of things when the 'input' is like this. With TI it would have been easy but no challenge either :)

I realize that the topic focuses too much on NextMember or other relative functions, rather than capturing subset elements.
I added this notion to the opening post and also the caveat of relying on the index order of dimension elements.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Post Reply