Visokio website     Downloads     Video tutorials     KnowledgeBase  
How can I classify records into quartiles or deciles? - Visokio Forums
How can I classify records into quartiles or deciles?
  • Guy_Cuthbert        Guy_Cuthbert December 4, 2008 9:05AM
    Omniscope does not yet have automatic grouping & ranking of data by numeric values (as of release 2.4) but it is relatively easy to create a formula field which will do the job for us.

    To create deciles for a selected field ("GrossUnits" in this example, just replace with your field name):

    10-INTFLOOR(RANK([GrossUnits],"GrossUnits")/(DATASET_NONEMPTYCOUNT("GrossUnits")/10))

    to create quartiles instead, simply replace each 10 with 4 i.e.

    4-INTFLOOR(RANK([GrossUnits],"GrossUnits")/(DATASET_NONEMPTYCOUNT("GrossUnits")/4)

    Breaking this down...

    1. Firstly we find out where each row ranks in the dataset, based on its GrossUnits value:
    RANK([GrossUnits],"GrossUnits")
    2. Then we calculate the number of rows of data which contain numeric values:
    DATASET_NONEMPTYCOUNT("GrossUnits")
    and then divide this by 4 or 10 (or any other integer e.g. 100 for percentiles) to calculate how many rows fit in each "band" of our grouping
    3. Then we divide the rank by the position by the number of records in each band, and round down the result:
    INTFLOOR(RANK([GrossUnits],"GrossUnits")/(DATASET_NONEMPTYCOUNT("GrossUnits")/4
    4. Finally, we subtract the result from our grouping to get the result - the very highest value in the dataset will get a rank of 1, which when divided by the number of records in a band (more than 1), and then rounded down, will result in 0... which when subtracted from our grouping value will place it in the top band, correctly.

    Note: The DATASET_NONEMPTYCOUNT function will not include rows where cells are blank (you will need to replace them with zeros to address this).
    Atheon Analytics Ltd
    w: www.atheonanalytics.com
    e: guy.cuthbert@atheon.co.uk
    t: +44 8444 145501
    m: +44 7973 550528
    s: guycuthbert
  • 3 Comments
  •     JamesDutton April 13, 2011 1:40AM
    Just tried this and found DATASET_NONEMPTYCOUNT doesn't work with v2.6, so replaced with:

    10-INTFLOOR(RANK([%field%],[%field%])/(ALLRECORDCOUNT/10))

    This seemed to work on my dataset..

    Cheers, J
  •     steve April 13, 2011 2:47AM
    DATASET_NONEMPTYCOUNT should still work with 2.6, but is deprecated so won't appear in the functions guide. What happened when you used it?

    It has been replaced with SUBSET and SUBSET_NONEMPTYCOUNT...
    http://www.visokio.com/kb/subset-functions
  •     steve November 9, 2011 4:55AM

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership