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

Options
Peter DeGregorio
Peter DeGregorio Customer
First Comment 5 Likes First 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 First Answer First Anniversary 5 Up Votes
    edited March 2020
    Options

    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
    First Comment 5 Likes First Anniversary
    Options

    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 First Answer First Anniversary 5 Up Votes
    edited March 2020
    Options

    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
    5 Up Votes First Comment 5 Likes First Anniversary
    Options

    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
    First Comment 5 Likes First Anniversary
    Options

    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 First Answer First Anniversary 5 Up Votes
    Options

    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

Categories