TM1 Forum

Posted: **Fri Apr 16, 2021 5:05 pm**

I have a dimension called DSD Month that lists out every month from 1996 to 2030. The element names go like "Mar 1996, Apr 1996, May 1996, …"

There's also an attribute called 'Year-Month' that goes like "1996-03, 1996-04, 1996-05, …"

For a particular subset I just want the end month of each quarter (Mar, Jun, Sep, Dec). I also need it sorted chronologically, of course.

My MDX is as follows:

Code: Select all

ORDER (
      
        UNION ( 
                  UNION (  TM1FILTERBYPATTERN ( TM1FILTERBYLEVEL ( TM1SUBSETALL ( [ DSD Month ] ) , 0 ) , "Mar *" ) 
                         , TM1FILTERBYPATTERN ( TM1FILTERBYLEVEL ( TM1SUBSETALL ( [ DSD Month ] ) , 0 ) , "Jun *" ) )
                , UNION (  TM1FILTERBYPATTERN ( TM1FILTERBYLEVEL ( TM1SUBSETALL ( [ DSD Month ] ) , 0 ) , "Sep *" )
                         , TM1FILTERBYPATTERN ( TM1FILTERBYLEVEL ( TM1SUBSETALL ( [ DSD Month ] ) , 0 ) , "Dec *" ) )
              )
       , [ DSD Month ].[Year-Month]
       , ASC
      )

The MDX works... sorta. I get the proper elements filtered but the sorting is wonky for 1996-1997, everything AFTER 1997 sorts fine.

It goes as follows:

Mar 1996
Mar 1997
Jun 1996
Jun 1997
Sep 1996
Sep 1997
Dec 1996
Dec 1997
Mar 1998
Jun 1998
Sep 1998
Dec 1998
…

Any ideas on why this would occur? Any thoughts on better means of approaching would be appreciated, also

Posted: **Fri Apr 16, 2021 5:42 pm**

Try change ASC to BASC

Order MDX
"...If BASC or BDESC is specified, the Order function arranges members in the set without regard to the hierarchy...."

Posted: **Fri Apr 16, 2021 6:17 pm**

You, good sir, are a gentleman and a genius. An ingenious gentleman.

I should've looked up that function, such a simple solution! It works perfectly now, thanks very much. n__n

TM1 Forum

MDX: Sorting on an attribute

MDX: Sorting on an attribute

Re: MDX: Sorting on an attribute

Re: MDX: Sorting on an attribute