Discussions

Expand all | Collapse all

Is there a way to rank values in quickbase similar to Excel's RANK function?

  • 1.  Is there a way to rank values in quickbase similar to Excel's RANK function?

    Posted 10-21-2016 17:35

    I need help ranking values from largest to smallest in Quickbase. In the past, I have used Excel's RANK function to achieve this, however I am coming up empty trying to mimic this function in Quickbase. A simple sort on the field will not work. I am analyzing data broken down by State and Regions, so I have multiple fields that I want to be able to see the ranking for, so that I can compare those rankings side by side without having to sort by each column. Thanks in advance for any help.



  • 2.  RE: Is there a way to rank values in quickbase similar to Excel's RANK function?

    Posted 10-21-2016 18:40
    You have to use script to do this. Feel free to contact me off-world using the information in my profile if you need personalized assistance to implement this.


  • 3.  RE: Is there a way to rank values in quickbase similar to Excel's RANK function?

    Posted 10-24-2016 14:16
    Thanks Dan! I just sent you an email.


  • 4.  RE: Is there a way to rank values in quickbase similar to Excel's RANK function?

    Posted 26 days ago
    Ranking formula using the new Query functions. Will rank records based on numeric field.  Ranks top 10, top 100, etc – or every value…

    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
    ------------------------------



  • 5.  RE: Is there a way to rank values in quickbase similar to Excel's RANK function?

     
    Posted 05-25-2017 22:06
    Hi Dan, I wasn't able to find your email. Would you be able to email me at info@ratespy.com? I have a similar need (to rank records) and need to hire someone to help. Thanks!


  • 6.  RE: Is there a way to rank values in quickbase similar to Excel's RANK function?

    Posted 20 days ago
    Hi Elwyn,

    Thanks for posting your solution.

    I have tried to use the code you provided above and I am getting the following error:

    There are one or more problems with your entry
    These function names are unknown.

    I've tried adding them using the "choose fields & functions", but still get an error.

    Any ideas what I need to check to get it working?

    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 = 44;                 // the field id of the numeric field being ranked - CHANGE AS NEEDED
    var number rankThis = [Total kWh/sqm];   // 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))​


    ------------------------------
    Michael Cronin
    ------------------------------



  • 7.  RE: Is there a way to rank values in quickbase similar to Excel's RANK function?

    Posted 20 days ago
    Michael
    Formula Queries is in Open Beta but you need to request access.  They are carefully opening up access to be able to assess any performance issues.  There is a link near the end of this page for the September 2021 Release Notes.

    https://help.quickbase.com/release-notes/september-2021-release-notes.html

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------