Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: Calculating the average of a 'top-N' subset? - Visokio Forums
Formulae: Calculating the average of a 'top-N' subset?
  • tagarw September 1, 2014 4:04AM
    Hi,
    I have a Table view in which data is changing according to the filters. For some selection suppose it has n number of records and 3 columns.

    For one column I want to calculate average of top 20% values of it. How to write the formula for the same?
  • 6 Comments
  •     tjbate September 1, 2014 5:56AM
    Jagran: Assuming you have used a 'Top_20' function to Rank and flag the Top_20 records in the subset, the formula would be a Subset_Mean function of the form:

    Subset_Mean([Field to be Averaged]),
    Subset2([FieldwithRanking],[AnotherField],
    'Top_20',[AnotherField],
    =,=)

    Note that in the Table View, the results of Formulae are global and do not respond to filtering, as they do inside views like the Content View.
  •     tjbate September 1, 2014 6:09AM
    Here is a link to the Top/Bottom 'n' discussions:

    http://forums.visokio.com/discussions/tagged?Tag=top%2Fbottom_n_records
  • tagarw September 1, 2014 7:50AM
    Thank you tjbate for replying back. Please see the attached file.

    I have used the formula
    IF(RECORDCOUNT()<=50, SUBSET_MEAN([Score],SUBSET([Category],"ORACLE")), 0)<br />
    My requirement is:
    1)If Filtered data has record count less then or equal to 50 then I want average score of all filtered records having category ="ORACLE".

    Problem is with else part where I have put 0 as of now.The requirement is:

    2)IF filtered data has records more then 50, then calclulate the average of top
    20% scores having category='ORACLE'.

    i.e. Suppose filtered data has 100 records then average of lowest 20 score having category='ORACLE'

    Please help.
    Attachments
    Current file.iok 15K
  •     tjbate September 1, 2014 3:07PM
    It is best to calculate whether a record's ranking is 'Top-20' or not/null in a separate formula field. If you do this in a Formula field using the RANK function named something like [RankingFormulaField], the rest of the expression is just another Subset function that merely looks at whether a record is both 'Top-20' AND 'ORACLE'.

    SUBSET-MEAN([Score]), SUBSET2([RankingFormulaField],[Category],
    'Top-20','ORACLE',
    =,=)

    Note that "top 20" records and "top 20%" of records (by what weighting?) are different concepts, and automatic Pareto rankings like Top 20% by defined weight are being discussed in the Ideas sections under the tag Pareto.
  • tagarw September 3, 2014 1:17AM
    Please attach one example.
  •     tjbate September 8, 2014 2:49PM
    Jagran - The attached is a general solution for defining top/bottom N records, and calculating sub-rankings and sub-averages for top/bottom n subsets.

Welcome!

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

Sign In Apply for Membership