Discussions

Expand all | Collapse all

Formula for Record Ranking

  • 1.  Formula for Record Ranking

    Posted 09-17-2019 13:33
    I am looking for advice on creating an automated numeric rank of records based on 2 fields. Here is an example of the data I'm working with and what the desired results are:

    Record Primary Code Secondary Code Rank
    Project A Medium Benefit $10,000 2
    Project B High Benefit $20,000 1
    Project C Low Benefit $5,000 3

    Project B is ranked 1 because it is in the High Benefit Category and it's Secondary Code value is higher than Project A.

    If I were to add a Project D with Primary Code=High Benefit and Secondary Code=$50,000, then Project D would be ranked 1 and the rest of the records would be shifted down one rank. 

    Appreciate any suggestions or advice!

    ------------------------------
    Courtney Rapp
    ------------------------------


  • 2.  RE: Formula for Record Ranking

    Posted 09-17-2019 13:43
    You can do this on a Summary report but no (or not easily) as actual field...  make a formula numeric field with a formula value of 1, 
    called [Rank].

    Make summary report and group it how you like, but ensure that every record is on its own line,.

    for example group by Primary code, and then by Secondary code and then by project name.  Include the [rank] field on the report but show as a running total down the column.

    The sort will probably work correctly if the Primary code if form a simple multiple cice field set to sort n the correct order.  Or else you will need to introduce a new formula field or lookup field off the benefit categories table to force the correct Alpha sort, such as 

    1. High Benefit
    2. Meduim Benefit
    3. Low Benefit.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 3.  RE: Formula for Record Ranking

    Posted 26 days ago
    This formula will rank records based on a numeric field - uses the new Query category of Functions

    var bool descOrder = true;            // true if ranking from high to low, otherwise false
    var number maxrank = 0;             // 0 to rank every number, else 10 to rank 1 through 10, 100 to rank 1 through 100, etc
    var number fieldId = 17;              // the field id of the numeric field being ranked - CHANGE AS NEEDED
    var number rankThis = [number field];  // the numeric field being ranked - CHANGE AS NEEDED
    var number rank    = If($descOrder, Size(GetCellValues(GetRecords("{"&$fieldId&".GT."&$rankThis&"}"),$fieldId))+1,
                                        Size(GetCellValues(GetRecords("{"&$fieldId&".LT."&$rankThis&"}"),$fieldId))+1);
    If($maxrank=0,$rank, If($rank<=$maxrank,$rank,null))

    ------------------------------
    Elwyn Dow
    ------------------------------



  • 4.  RE: Formula for Record Ranking

    Posted 25 days ago

    Elwyn,
    thanks for posting this and showing hope. 

    I tried your formula on are it's ID field with fieldid=3

    i simply get numeral 1 as the rank in field instead of 1,2,3 etc 



    ------------------------------
    Prashant Maheshwari
    ------------------------------