TM1 Forum

I have a VENDOR dimension that is very flat - just a list of elements and a subtotal.

I want it to ALPHA sort ascending under the total BUT I need one specific element to appear 1st (and of course, it's not first alphabetically).

EXAMPLE:
Parent: TOTAL - VENDORS
Child 1: A
Child 2: B
Child 3: C
Child 4: Make me 1st
Child 5: Z


I want the subset to appear as follows:
Parent: TOTAL -VENDORS
Child 4: Make me 1st
Child 1: A
Child 2: B
Child 3: C
Child 5: Z

As new elements are added dynamically through a TI Process to a data source, they would continue to alpha sort under CHILD 1.

How can I do this?

That's going to require a three part MDX query joined together by a + sign

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [VENDOR] )},"TOTAL - VENDORS")}+
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [VENDOR] )},"Child 4")}+
{EXCEPT({TM1SORT({TM1FILTERBYLEVEL( {TM1SUBSETALL([VENDOR])},0)},ASC)},{[VENDOR].[Child 4]})}