Median in tm1 using mdx sort

Post Reply
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Median in tm1 using mdx sort

Post by Analytics123 »

Hi ,

I need to find a median for customers in a 12 month period sales . I know we dont have a formula in tm1 for median . so for work around ,

I have the cube with customers, 12 month sales data. I am planning to write a ti process on the customer subset .

Take each customer and write a mdx which take that customer and the the 12 months and sort it by the sales amount in ascending order.

Then I would take subset's 5th and 6th element sales and divide by 2 and store the value .

Can anyone please tell me the mdx to sort the customer sales by 12 month to do a subsetcreatemdx .

{
ORDER(
{ TM1FILTERBYLEVEL(
{TM1SUBSETALL( [Product] )}
,0)}
, [Test].([Posting Measures].[Amount]), BDESC)
}

Instead of all product here I should give my tuple of a single customer and 12 months

How do i modify the above mdx

Thanks,
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Median in tm1 using mdx sort

Post by Analytics123 »

Okay I got this working to sort my months based on a customer sale .

{
ORDER(
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({[Months].[FY2017]}, ALL, RECURSIVE )},0)}
, [testcube].([Sales Measures].[Full Revenue],[testcty].[usa]), BASC)
}


This returns me months in ascending order with sales value for USA .

Now i have to pick the fifth and 6 th element and then do a cell get n and do an average of 2 .

Is there a way to directly get the 5 th and 6th order value in the set ?

Thanks,
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Median in tm1 using mdx sort

Post by Wim Gielis »

Euh... SubsetGetElementName...
Did you check the available functions ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Median in tm1 using mdx sort

Post by Analytics123 »

yeah , i was asking in the below scenario it returns me the sorted months by sales, is there was a way I could directly get the sales value directly ,

without using cellgetn

Thanks,
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Median in tm1 using mdx sort

Post by gtonkin »

Analytics123 wrote: Thu Oct 05, 2017 1:43 pm ...Is there a way to directly get the 5 th and 6th order value in the set ? ...
Maybe not the exact values as you are dealing with a list of elements and not cells. However this may at least narrow your list:

Code: Select all

HEAD(
TAIL(
{
ORDER(
  {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({[Months].[FY2017]}, ALL, RECURSIVE )},0)}
, [testcube].([Sales Measures].[Full Revenue],[testcty].[usa]), BASC)
}
,7)
,2)
edit: assuming 12 months with median at 6.5-this will give you period 6 and 7. Adjust the 7 as required.
Post Reply