Active form MDX Order

[rameez]

Hi,
I have an active form with a requirement to sort on multiple levels like in excel (sort by col1 then by col2).
The active form needs to be sorted by the value in the cube based on selected parameters (SUBNM).
In the rows i have territory dimension at the level zero. in the columns are Amount & Tier.
Each territory belongs to Tier 1, 2, OR 3.
The active form needs to be sorted 1st based on tier and then on amount in descending order.

Here is the MDX statement in the TM1RPTROW formula.

Code: Select all

{ORDER(  {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Territory] )}, 0)}, [Cube].([Category].["&$E$15&"], [Periods].["&$G$1&"], [Measure].[Tier]), BDESC)}					

Is there a way to achieve this?
Any help is appreciated.

[EvgenyT]

{ORDER( {ORDER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Territory] )}, 0)}, [Cube].([Category].["&$E$15&"], [Periods].["&$G$1&"], [Measure].[Tier]), BDESC)},
[Cube].([Category].["&$E$15&"], [Periods].["&$G$1&"], [Measure].[Amount]), BDESC)}

Thanks

[rameez]

Thanks,
That is exactly what i thought would work. But the outer order resorts the data based on the values in Amount column, breaking the inner ordered set.

[declanr]

Standard MDX does allow for nesting order statements in a simple way; TM1 MDX is a little more complex... by which I mean that TM1 MDX is much more basic and therefore has limitations.

I was certain that I had done something like this before using the generate statement to produce the groupings based on the first order; and then sorting each of those in the second part of the generate with another order... but I can't find that code or quite get it working right now; I might manage to work it out again later but have limited time to spend on it right now.

To get to your solution quickly you just need to think in basic arithmetic terms; for example make the second sort value be a decimal point on the end of the first and then sort it all in 1 go. So if you have rows where the values on the first row are 1 and 2 and the values on the second are 1 and 3 - you can order them as 1.2 and 1.3.
Something like this can be done by throwing a rule in to place to concatenate your 2 values or just do it in the MDX.

I also can't seem to get CStr working right now which is driving me mad but again going even more basic on arithmetic try something like:

Code: Select all

{ORDER( 
	{TM1FILTERBYLEVEL( {[Territory].members}, 0)}, 
	[Cube].([Category].["&$E$15&"], [Periods].["&$G$1&"], [Measure].[Tier]) + 
	( [Cube].([Category].["&$E$15&"], [Periods].["&$G$1&"], [Measure].[Amount]) / 1000000000000000 ) , 
	BDESC
)}

Not the most elegant but functional.

[declanr]

I should have mentioned; as you have probably already guessed the reason that the MDX you tried previously with a simple nested order did not work is because the outer order is just working on the entire set passed to it from the inner order. It pays no attention to whether the inner set is already order or not.

[EvgenyT]

Thanks,
That is exactly what I thought would work. But the outer order resorts the data based on the values in Amount column, breaking the inner ordered set.

Apologies, misunderstood your requirements... could you it split into two subsets with SubsetToSet? I.e. subset1: Order by Tier, subset2: Order(SubsetToSet(subset2)) ....

I haven’t tried it myself, but could be another avenue to explore... probably not the most efficient and elegant way to do it either, but just to see whether you can arrive to the desired result with some TM1 MDX method

Thanks

[EvgenyT]

Hi Guys,

Following my previous post, I can confirm that SubsetToSet didnt return desired results.

I played around with Generate, but didnt have any luck either. However, following Declar's logic, this workabout should address your issue in the interim (not elegant either):

1. I used simple MDX: {ORDER({TM1SUBSETALL( [Product] )},[MDX Test].([Test Measure].[Sys]),BDESC)}
2. Created consol Sys
3. Gave amount a weighting of 0.00001

[img]

MDX Order.png (36.76 KiB) Viewed 9695 times

[/img]

However, not that versatile and lacks an ability to do DESC by Tier and ASC by Product simultaneously

[rameez]

Thanks,
Not the most elegant solution but it works. I was hoping to achieve this without the need of an extra element.

[declanr]

Thanks,
Not the most elegant solution but it works. I was hoping to achieve this without the need of an extra element.

The MDX example I provided doesn't require an extra element.