Forum Discussion
ElwynDow1
Qrew 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
------------------------------
MichaelGibson
3 years agoQrew Member
Elwyn. Thank you for this code. It almost worked right off the copy. Though I am getting several things ranked 10... even though they might have a score of 19.2 vs 19.4 to compare from. Maybe only integer comparison?
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 = 73; // the field id of the numeric field being ranked - CHANGE AS NEEDED
var number rankThis = [Average Total Score]; // the numeric field being ranked - CHANGE AS NEEDED
So is FieldId 73 is [Average Total Score] in my case. Should it be different?
var number rank = If($descOrder, Size(GetFieldValues(GetRecords("{"&$fieldId&".GT."&$rankThis&"}"),$fieldId))+1,
Size(GetFieldValues(GetRecords("{"&$fieldId&".LT."&$rankThis&"}"),$fieldId))+1);
I notice it was throwing a soft error on "GetCellValues" so I switched it to GetFieldValues and seemed to work similarly.
If($maxrank=0,$rank, If($rank<=$maxrank,$rank,null))
I really appreciate the hard work and know that I need to put more work into learning language.
------------------------------
Michael Gibson
------------------------------
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 = 73; // the field id of the numeric field being ranked - CHANGE AS NEEDED
var number rankThis = [Average Total Score]; // the numeric field being ranked - CHANGE AS NEEDED
So is FieldId 73 is [Average Total Score] in my case. Should it be different?
var number rank = If($descOrder, Size(GetFieldValues(GetRecords("{"&$fieldId&".GT."&$rankThis&"}"),$fieldId))+1,
Size(GetFieldValues(GetRecords("{"&$fieldId&".LT."&$rankThis&"}"),$fieldId))+1);
I notice it was throwing a soft error on "GetCellValues" so I switched it to GetFieldValues and seemed to work similarly.
If($maxrank=0,$rank, If($rank<=$maxrank,$rank,null))
I really appreciate the hard work and know that I need to put more work into learning language.
------------------------------
Michael Gibson
------------------------------