Results 1 to 6 of 6

Thread: Percentile with MDX ???

  1. #1
    Join Date
    Dec 2007
    Posts
    2

    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

  2. #2
    Join Date
    Nov 2009
    Posts
    2
    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

  3. #3
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Are We Talking Essbase here?

    Just making sure ...

    Bill

  4. #4
    Join Date
    Nov 2009
    Posts
    2
    Hi,
    We are talking about MDX query used to calculate percentile...
    Atleast this is what I understand from the post above.

    thanks

  5. #5
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    The Percentile Function in Essbase?

    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

  6. #6
    Join Date
    Dec 2007
    Posts
    2

    Percentile with MDX ???

    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])

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •