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,
Median in tm1 using mdx sort
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
-
- 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
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,
{
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,
-
- 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
Euh... SubsetGetElementName...
Did you check the available functions ?
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
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
-
- 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
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,
without using cellgetn
Thanks,
- 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
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: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 ? ...
Code: Select all
HEAD(
TAIL(
{
ORDER(
{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({[Months].[FY2017]}, ALL, RECURSIVE )},0)}
, [testcube].([Sales Measures].[Full Revenue],[testcty].[usa]), BASC)
}
,7)
,2)