MDX expression for NextMember in case of variable source member

[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 ]

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 (13.58 KiB) Viewed 13531 times

A subset called 'Current week' contains the... current week. Suppose we take Wk_06_2019:

02.png (10.72 KiB) Viewed 13531 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 (11.11 KiB) Viewed 13531 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

[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 (44.43 KiB) Viewed 13527 times

Without the other dimension in the model, it works fine:

05.png (26.75 KiB) Viewed 13527 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

[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 (28.2 KiB) Viewed 13511 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 (65.54 KiB) Viewed 13509 times

Happy TM1'ing !

Wim

[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...

[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.

[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.