Percentile with MDX ???

How to create Percentile with MDX??

i got this function but i'm not success to follow up.

Percentile ( set, numeric_value_expr, percentile )

(set -> The set from which to get a tuple value.
numeric_value_expr -> A numeric value or an expression that returns a numeric value.
percentile -> A percentile. Must be between 0 and 100. )


Thank you

Hello Aftera,

Wonder if you found the solution to this? I got some answer, but it does not look quite like what I have expected it to be
please help
naaziz

Just making sure ...

Bill

Hi,
We are talking about MDX query used to calculate percentile...
Atleast this is what I understand from the post above.

thanks

The only Percentile function with which I have any exposure is the Essbase MDX function. Is this the function to which you are referring?

Example:

WITH MEMBER [Measures].[Perc] AS
'Percentile(Products.Levels(0).Members, [Measures].[Price Paid], 10)'
SELECT {[Measures].[Price Paid], [Measures].[Perc] } ON COLUMNS,
{ Products.Levels(0).Members } ON ROWS
FROM AsoSamp.Sample


Per the documentation, "this function orders the set according to the numeric value expression. It then finds the tuple at the given percentile and returns its value ... The returned value is such that n percent of the set members are smaller than it."

Is this what you are trying to accomplish? Where are you trying to use the function?



Thanks.

Bill

Hello naaziz,

for my Percentile , i didn't use mdx function.
i created 5 variables for calculate Percentile follow stat step.

a. find position of P25
b. rank member min to max
c. calculate proportion

(i think it not the best way because it's not flexible and create with long syntax. i think mdx function it's the best way but i cannot create with it)

example -Percentile 25

1.[Sales Rank]

iif(IsEmpty([Measures].[Sales_value]), NULL,
Rank([Dept Member].[Dept Member].CurrentMember,
Filter(Order([Dept Member].[Dept Member].CurrentMember.Level.Members,
([Measures].[Sales_value]), BASC),
isempty([measures].[Sales_value])=0)))


2.[Dim_N]
count(Filter( Order([Dept Member].[Dept Member].[Dept Name].Members, [Dept Member].[Dept Member].CurrentMember.Name, DESC) ,
([Measures].[Sales Rank] <> null ))

3.[PR-25]
25*([Dept Member].[Dept Member].[Dim_N]+1)/100

4.[Dim_Percentile 25]
MAX(Filter( Order([Dept Member].[Dept Member].[Member Name].Members, [Dept Member].[Dept Member].CurrentMember.Name, DESC)
,([Measures].[Sales Rank] = Int([Dept Member].[Dept Member].[PR-25]))))+((MAX(Filter( Order([Dept Member].[Dept Member].[Member Name].Members, [Dept Member].[Dept Member].CurrentMember.Name, DESC) ,
([Measures].[Sales Rank] = Int([Dept Member].[Dept Member].[PR-25]+1))))- MAX(Filter( Order([Dept Member].[Dept Member].[Member Name].Members, [Dept Member].[Dept Member].CurrentMember.Name, DESC) ,([Measures].[Sales Rank] = Int([Dept Member].[Dept Member].[PR-25]))))
)*([Dept Member].[Dept Member].[PR-25]-int([Dept Member].[Dept Member].[PR-25])))


5.[Percentile 25]
([measures].[Sales_value],[Dept Member].[Dept Member].[Dim_Percentile 25])