TM1 Forum

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,

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,

Euh... SubsetGetElementName...
Did you check the available functions ?

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,

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: edit: assuming 12 months with median at 6.5-this will give you period 6 and 7. Adjust the 7 as required.