MDX expression for NextMember in case of variable source member

Post Reply
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

MDX expression for NextMember in case of variable source member

Post by Wim Gielis »

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 12112 times
A subset called 'Current week' contains the... current week. Suppose we take Wk_06_2019:
02.png
02.png (10.72 KiB) Viewed 12112 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 12112 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

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis »

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 12108 times


Without the other dimension in the model, it works fine:
05.png
05.png (26.75 KiB) Viewed 12108 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

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis »

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 12092 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. You should use level002, however, if you did not specify a name for that level (within the given dimension/hierarchy)

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

Happy TM1'ing !

Wim
Last edited by Wim Gielis on Wed Oct 09, 2019 2:32 pm, edited 2 times in total.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

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

Post by Steve Rowe »

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...
Technical Director
www.infocat.co.uk
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis »

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

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis »

Coming back to this for a second, and noting that relying on the index order of elements is not the best thing to do.

This works too to get the next element in a dimension/hierarchy based on an element stored in a subset:

Code: Select all

{ TM1Member( [Week].[Current week].Item(0), 0).NextMember }
Not sure I understand why I was overcomplicating this one.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply