Another MDX question

[marboy]

I'm sure this is quite easy but it's driving me crazy.

I have a period dimension (all dates and all the usual hierarchies). I want to create a dynamic subset based on the user inputting a date range. I have used the 'LastPeriods' function to take the end date entered by the user and count back the relevant number of days.

Example: { LastPeriods(10, [period].[18/07/2011]) }

This worked really well until the date range was greater than 31 days, then it started bringing in the higher level elements (Namely Year to dates)

Sooo, I can get the lowest level with

{TM1SORTBYINDEX( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)}, ASC)}

How can I join the two together to get any date range I want? I know Union and Intersect won't work. Perhaps I should change my approach but this just seemed intuitively correct.

Help?

[rozef]

You can try:

{ FILTER( { LastPeriods(10, [period].[18/07/2011]) }, [period].CurrentMember.Level.Ordinal = 0)}

but the ordinal function dont seem to works properly functions of TM1 versions.
I am surprised UNION or INTERSECTION doesn't work.

[tomok]

I have a period dimension (all dates and all the usual hierarchies). I want to create a dynamic subset based on the user inputting a date range. I have used the 'LastPeriods' function to take the end date entered by the user and count back the relevant number of days.

The problem you are having here is that the TM1 version of LastPeriods doesn't really have any date intelligence in it. All it does is return the last number of elements in the dimension, at the same level in the hierarchy as the one specified. The reason you aren't getting any dates outside of the current month is probably due to the hiearchy structure of your PERIOD dimension. If you were to have all your days rolling into a year, and not have each rolling into a month first, the function might work like you want. Certainly not a very good solution. This is why I almost never use the LastPeriods function in any MDX, it's not really reliable for what you want to use it for.

[marboy]

ok, I couldn't get
{ FILTER( { LastPeriods(10, [period].[18/07/2011]) }, [period].CurrentMember.Level.Ordinal = 0)}
to work.

UNION and INTERSECTION can't work since the LastPeriods function returns not only n level dates but consolidations such as YTD and I only want the n level.

I'm wary of resturcturing the dimension since just about every cube/report we have relies on it.

I've spent ages on this now - there has to be a better way !

[rmackenzie]

I've spent ages on this now - there has to be a better way !

Interesting question... in fact, I don't have any problem with your original statement with my date dimension. The critical difference is that my n-level elements are structured YYYY-MM-DD not DD/MM/YYYY. When I do:

Code: Select all

{ LastPeriods(45, [period].[18/07/2011]) }

The resulting subset goes back to December and ignores the intermediary consolidations of 2011-02 (parent of 28 2011-02-xx n-level elements), 2011-01 (parent of 31 2011-01-xx n-level elements) and 2011 (parent of 12 2011-xx consolidated elements).

I guess I should try with some YTD roll-ups too, but I am wondering if this is to do with your date format...?

Another advantage of YYYY-MM-DD format from http://en.wikipedia.org/wiki/Lexicographical_order:

An important exploitation of lexicographical ordering is expressed in the ISO 8601 date formatting scheme, which expresses a date as YYYY-MM-DD. This date ordering lends itself to straightforward computerized sorting of dates such that the sorting algorithm does not need to treat the numeric parts of the date string any differently from a string of non-numeric characters, and the dates will be sorted into chronological order. Note, however, that for this to work, there must always be four digits for the year, two for the month, and two for the day, so for example single-digit days must be padded with a zero yielding '01', '02', ... , '09'.

This means that you can use this MDX in your dynamic subset to get a range of elements:

Code: Select all

{[Period].[2011-01-15] : [Period].[2011-02-15]}

For me, the output subset also ignores the intermediary consolidations which exist between the last day and first day (when the dimension is viewed in index order).

Without wanting to hector, I believe using YYYY-MM-DD is probably a TM1 'best practice' because internally TM1 deals with dates-as-strings in this format. However, I do recognise it is much less intuitive for users than the more typical DD/MM/YYYY format.

Robin

[marboy]

It seems the problem only occurs with July dates (accounts for why I missed it when I was testing). I have YYYMMDD as an alias. Following is the result for { LastPeriods(10, [period].[05/07/2011]) }

2016LTD
2017LTD
2018LTD
2019LTD
2020LTD
20110701
20110702
20110703
20110704
20110705

All this is leading to the dimension structure again

[rmackenzie]

If you look at the dimension index for 20110701 is the dimension index for 2020LTD just that -1?

[rozef]

It sound like rmackenzie is right, you should have YYYY-MM-DD elements to can use date funtions.

You can still make:

{ UNION(
{ UNION(
{ UNION(
{ [Date].[18/07/2011]},
{ [Date].[18/07/2011].PrevMember }),
{ [Date].[18/07/2011].PrevMember.PrevMember })
... x10

I know it's ugly but it will work.

[marboy]

Rozef, I think your solution wouldn't be viable for 365 days.

rmackenzie, no ! the index for 1 july is 2685 and 2020LTD is 6254 ..... curiouser and curiouser! This makes me think that my original idea of trying to combine the Lastperiods function with the sortbyindex at level 0 would be a logical way to go - but I just can't do it.

[rmackenzie]

LastPeriods takes a member (element), not a set (subset), so I'm not sure how to make it work with the n-level subset.

What you could do, however, is make a replica Period dimension with only n-level elements in it and have it in the correct chronological and index order. When you use the LastPeriods function on an element in this replica dimension you should get the correct subset (no dimension structure issues to deal with). You can then copy this subset into your original Period dimension because all the n-level elements exist in both dimensions.

It is a bit of a clumsy workaround, but not totally impractical. It also avoids the need to tinker with your current Period dimension which I guess is widely used in your model.

<edit>

Interesting, it seems LastPeriods implicity works at the level of the supplied member - see my example at level 1 of my period dimension. This makes me think your issues are definitely related to dimension structure. See here:

[marboy]

Thanks for that. This looks like a workable alternative and closer to the way I was thinking about it. I won't get chance to do it today but thankyou for all your help.

[marboy]

Just for the sake of completeness, here is how I've solved the issue - thanks to all who responded, I wouldn't have got there without you!

My Period dimension has an attribute 'Movex_serial' which is just that - the serial number assigned to a date in Movex. I just used this to get the n level elements I needed.

INTERSECT(
{filter({TM1FILTERBYLEVEL({TM1SUBSETALL( [Period] )},0)},[period].[Movex_serial]>= '|vMovexFrom|')},
{filter({TM1FILTERBYLEVEL({TM1SUBSETALL( Period] )}, 0)},[period].[Movex_serial]<='|vMovexTo|')})'

Where vMovexFrom and vMovexTo are defined ranges in the report

Really easy once you know!

Thanks again.

[rozef]

Good job.

Just for information you can also do

{filter({TM1FILTERBYLEVEL({TM1SUBSETALL( [Period] )},0)},
( [period].[Movex_serial]>= '|vMovexFrom|' )
AND ( [period].[Movex_serial]<='|vMovexTo|') )}

I dont know if it's a better way then an intersection but probably easier to write if you have a lot of condition.

[marboy]

Yes, that's much neater - thankyou again !