Acedemic exercise to understand MDX

[AmbPin]

Hello,

I have been playing with MDX to try and understand if it is possible to make dynamic time based filters to say create a subset containing the last few days. MDX seems to have no concept of now() or today() so for this excercise I have added a rule based flag to a time dimension that denotes today as you can see below:-

TimeDim.PNG (3.68 KiB) Viewed 6540 times

I can create a filter that makes a subset that contains just this element:-

FilterStatement.PNG (6.5 KiB) Viewed 6540 times

However my question is can I use this in any way to create a subset that retrieves the last few days using something like the attempt below:-

Capture.PNG (8.41 KiB) Viewed 6540 times

I know there are many ways to create such a subset using rules/TI Etc. but I really wanted to see if this was possible with MDX. Any hints or tips would be most gratefully recieved.

[Nick_Blommaerts]

My knowledge on MDX is rather small, as far as I know you'll always need to filter on something somehow, whether it is an attribute or a dimension element value/level/..

What I'd do in this case though, at first glance, is create a dummy element in your time dimension. Then via an entry form or straight into the cube, you could store the time interval on which you want to base your subset in that dummy (say, x amount of days to be shown, contain from today backwards). If you have that value and the current day, it should be quite easy to alter your current rule to store a "1" value in all days you want to see displayed.

It's not purely via MDX in this case though :/ It should be interesting to see if anyone can actually come up with something like that.

[AmbPin]

Thanks Nick,
Yes I had thought of that and a way where I could make my jTodayFlag rule index backwrds from today so I could select filter on where jTodayFlag < X.
As you say though it would be interesting if anyone can point us in the right direction to do this in MDX.

[rozef]

AmbPin,

your MDX request fail because LASTPERIODS function expect a member in second argument and FILTER function return a set.
You need to transform the result with TM1Member to return a valid member expression:

LASTPERIODS( 2,
TM1Member( { FILTER( ... )}.Item(0), 0)
)

You could probably also use Nick solution with a rule based on date numeric identifiers.
There is a TM1 function which can display them.

Cheers.

[AmbPin]

Hello,
Thanks I had tried that but dont seem to be able to make it work even with a straight forward statement such as:-
TM1MEMBER( { [2012-04-19] }.item(0), 0)

[AmbPin]

Oh I see, TM1MEMBER( { [2012-04-19] }.item(0), 0) doesnt work on its own but does in the lastperiods(2, TM1MEMBER( { [2012-04-19] }.item(0), 0))
Thanks very much

[tomok]

Oh I see, TM1MEMBER( { [2012-04-19] }.item(0), 0) doesnt work on its own but does in the lastperiods(2, TM1MEMBER( { [2012-04-19] }.item(0), 0))
Thanks very much

You need to be aware that this function is returning those elements based on their index in the dimension, compared to the member "2012-04-19". As long as your dimension is sorted in date order, so that each successive day has an index number higher than the day before then you will be OK. If not, then you may get incorrect results. It's basically the same as using the DIMIX function. The LASTPERIODS function is misleading in that it isn't really "date aware", it has no idea that your member named "2012-04-19" means April 19, 2012. If it did, wouldn't there need to be some standards around element naming? How would TM1 know that '2012-04-19', '04/19/2012', '19/04/2012" all mean the same thing? Think about it.

[lotsaram]

I'm not sure where this stands in the context of "best practice" but the way I manage it is to have a 2D control cube which has rule calculated date measures.

For Example:
In the SYS_Date cube ...

Code: Select all

['SYS_Date_M':'System Date', 'Value_Type':'String'] = S: TimSt(NOW, '\Y-\m-\d');
['SYS_Date_M':'System Month', 'Value_Type':'String'] = S: TimSt(NOW, '\M');

And then this kind of dynamic subset in various time dimensions ...

Code: Select all

{FILTER(
  {TM1SUBSETALL( [Month] )},
  [Month].CurrentMember.Name = SYS_Date.([SYS_Date_M].[System Month], [Value_Type].[String])
)}

(Due to locking concerns with dynamic subsets frequently I do away with the dynamic subsets and just undertake the same logic in TI and populate static subsets.)

[AmbPin]

Thanks, that is a good approach.
I have TI processes populating static subsets and am unlikely to move away from that, but became interested when playing.

Incidentally, is there a function to get now/today in MDX?

[AmbPin]

In the SYS_Date cube ...

I assume that this is a cube you have created yourself?

[Martin Ryan]

It will be, any cube that is created by TM1 has } in front of it. They're known as control cubes. "sys" and "z" are commonly used prefixes for system administration cubes that have been built by the developer.

You can, by the way, create your own control cubes simply by putting a } at the front of the cube name when you create it, but the trouble is that they are usually then hidden. My own preference (and I think it's a fairly common practice) is to stick with sys or z cubes but hide them from the general user population through security.

Martin