Forum Discussion
ElwynDow1
4 years agoQrew Trainee
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
------------------------------
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
------------------------------
MichaelCronin
4 years agoQrew Member
Hi Elwyn,
Thanks for posting your solution.
I have tried to use the code you provided above and I am getting the following error:
------------------------------
Michael Cronin
------------------------------
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?
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
------------------------------
- MarkShnier__You4 years ago
Qrew Legend
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
------------------------------