Need to compute percentiles. How to do this in Board?

Peter DeGregorio
Peter DeGregorio Customer
25 Likes 10 Comments Second Anniversary
in Platform

Hello, 

For a given set of values we need to know the percentile which corresponds to each value. Cumulated Vertical % does not seem to help but I could be missing something. Say I want to know whether a given cube value is in 75th percentile or better? 

Thank you

Answers

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers
    edited March 2020

    Hi Peter,

     

    I think you can do this with the 80-95 Ranking function or on a sorted DV (according to your figure) or using Cumulated Vertial (also on a sorted DV) together with an Algortihm to specify your percentile

    image

     

    regards

    Björn

  • Peter DeGregorio
    Peter DeGregorio Customer
    25 Likes 10 Comments Second Anniversary

    Hello Björn,

     

    Thank you for your reply. In the DV example you give the top two entries are 75 percentile or greater. The middle entry "fixed blade" is at 50th percentile (the median). The trouble I'm having with CV and 80/95 is they are concerned with value and percentile is concerned with position in the ranked list only. 

     

    Does Board have an easy way to indicate median? In effect the 75th percentile is the median of the values above the median of all of the values. 

     

    The term I'm asking about is described here:

    Percentile - Wikipedia 

    The generic term is quantile. 

     

    Regards,

    Pete

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers
    edited March 2020

    Hi Peter,

     

    ok now I understand. I think you can easily solve this using NEXEL (Nexel Complete Formulae library )

    MEDIAN: =MEDIAN( [@a;*;*;Range.Vertical] )

    25% Percentile: =Percentile( [@a;*;*;Range.Vertical] , 0.25)

     image

    Regards

    Björn

  • Previous Member
    Previous Member
    500 Up Votes 500 Likes 500 Comments Second Anniversary

    You may have a look at the entire library of the available NEXEL formulas and syntax here

    https://community.board.com/docs/DOC-1241-nexel-complete-formulae-library#comment-1151

    Cheers, Diego 

  • Peter DeGregorio
    Peter DeGregorio Customer
    25 Likes 10 Comments Second Anniversary

    Thank you this looks promising. I'll look into it. Ultimately I need to get a result which uses the percentile value back into Board as a dimension: such as "fixed blades" is 50th percentile. Is the Nexcel layer on a screen available for this kind of thing?

     

    (Mobile)

    Peter DeGregorio

    Information Technology

    ITW Global Brands

    Hartford CT

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers

    Hi,

     

    you can write back the result of the nexel layer to an cube or also can set an Relationship using nexel (Nexel ).

    But keep in mind, that the Percentile is depending on the number of selected elements, so it might be better, to write back those values back to an cube (structured on an user dimension), cause otherwise the solution wouldn't be multi user ready

     

    regards

    Björn

  • Cristiano
    Cristiano Active Partner
    First Comment

    Hello, I found the Nexel formula very useful, but it works till there are some dozen, may be some hundred lines. I tried to use it to calculate the median of all orders, 20-30.000 lines and it doesn't work

    Any suggestion?

    Thanks

    Cristiano

Categories