NathanAllan3
Qrew Trainee
3 years ago

# Finding the 2nd Max or 2nd Min

I've been using the new formula queries which are awesome! One thing they can't do however is aggregate the data the pull. So I have a formula which then will do it for me. I need to find the lowest, 2nd, and 3rd lowest values of all the sibling records in a table and there is no set number of sibling records for any given record. I've built it to handle up to 20 at this point. So far I am able to extract the lowest, but I get stuck when I'm trying to find the 2nd or 3rd lowest values. Here's what I have so far. Any help would be greatly appreciated!

var text valuelist = SearchAndReplace(ToText(GetCellValues(GetRecords("{8.EX.'" & [Related Project] & "'}"),51)),"\$","");

var number valueone = ToNumber(Part(\$valuelist, 1, ";"));
var number valuetwo = ToNumber(Part(\$valuelist, 2, ";"));
var number valuethree = ToNumber(Part(\$valuelist, 3, ";"));
var number valuefour = ToNumber(Part(\$valuelist, 4, ";"));
var number valuefive = ToNumber(Part(\$valuelist, 5, ";"));
var number valuesix = ToNumber(Part(\$valuelist, 6, ";"));
var number valueseven = ToNumber(Part(\$valuelist, 7, ";"));
var number valueeight = ToNumber(Part(\$valuelist, 8, ";"));
var number valuenine = ToNumber(Part(\$valuelist, 9, ";"));
var number valueten = ToNumber(Part(\$valuelist, 10, ";"));
var number valueeleven = ToNumber(Part(\$valuelist, 11, ";"));
var number valuetwelve = ToNumber(Part(\$valuelist, 12, ";"));
var number valuethirteen = ToNumber(Part(\$valuelist, 13, ";"));
var number valuefourteen = ToNumber(Part(\$valuelist, 14, ";"));
var number valuefifteen = ToNumber(Part(\$valuelist, 15, ";"));
var number valuesixteen = ToNumber(Part(\$valuelist, 16, ";"));
var number valueseventeen = ToNumber(Part(\$valuelist, 17, ";"));
var number valueeighteen = ToNumber(Part(\$valuelist, 18, ";"));
var number valuenineteen = ToNumber(Part(\$valuelist, 19, ";"));
var number valuetwenty = ToNumber(Part(\$valuelist, 20, ";"));

var number lowest = Min(\$valueone,\$valuetwo,\$valuethree,\$valuefour,\$valuefive,\$valuesix,\$valueseven,\$valueeight,\$valuenine,\$valueten,\$valueeleven,\$valuetwelve,\$valuethirteen,\$valuefourteen,\$valuefifteen,\$valuesixteen,\$valueseventeen,\$valueeighteen,\$valuenineteen,\$valuetwenty);

var number secondlowest = Min(\$valueone,\$valuetwo,\$valuethree,\$valuefour,\$valuefive,\$valuesix,\$valueseven,\$valueeight,\$valuenine,\$valueten,\$valueeleven,\$valuetwelve,\$valuethirteen,\$valuefourteen,\$valuefifteen,\$valuesixteen,\$valueseventeen,\$valueeighteen,\$valuenineteen,\$valuetwenty);

var number thirdlowest = Min(\$valueone,\$valuetwo,\$valuethree,\$valuefour,\$valuefive,\$valuesix,\$valueseven,\$valueeight,\$valuenine,\$valueten,\$valueeleven,\$valuetwelve,\$valuethirteen,\$valuefourteen,\$valuefifteen,\$valuesixteen,\$valueseventeen,\$valueeighteen,\$valuenineteen,\$valuetwenty);

\$lowest

------------------------------
Nathan Allan
------------------------------

• Got it. For anyone interested in a solution similar to this here you go.

var text valuelist = SearchAndReplace(ToText(GetCellValues(GetRecords("{8.EX.'" & [Related Project] & "'} AND {51.XEX.'" & null & "'} AND {51.XEX.'" & 0 & "'}"),51)),"\$","");

var number valueone = ToNumber(Part(\$valuelist, 1, ";"));
var number valuetwo = ToNumber(Part(\$valuelist, 2, ";"));
var number valuethree = ToNumber(Part(\$valuelist, 3, ";"));
var number valuefour = ToNumber(Part(\$valuelist, 4, ";"));
var number valuefive = ToNumber(Part(\$valuelist, 5, ";"));
var number valuesix = ToNumber(Part(\$valuelist, 6, ";"));
var number valueseven = ToNumber(Part(\$valuelist, 7, ";"));
var number valueeight = ToNumber(Part(\$valuelist, 8, ";"));
var number valuenine = ToNumber(Part(\$valuelist, 9, ";"));
var number valueten = ToNumber(Part(\$valuelist, 10, ";"));
var number valueeleven = ToNumber(Part(\$valuelist, 11, ";"));
var number valuetwelve = ToNumber(Part(\$valuelist, 12, ";"));
var number valuethirteen = ToNumber(Part(\$valuelist, 13, ";"));
var number valuefourteen = ToNumber(Part(\$valuelist, 14, ";"));
var number valuefifteen = ToNumber(Part(\$valuelist, 15, ";"));
var number valuesixteen = ToNumber(Part(\$valuelist, 16, ";"));
var number valueseventeen = ToNumber(Part(\$valuelist, 17, ";"));
var number valueeighteen = ToNumber(Part(\$valuelist, 18, ";"));
var number valuenineteen = ToNumber(Part(\$valuelist, 19, ";"));
var number valuetwenty = ToNumber(Part(\$valuelist, 20, ";"));

var number lowest = Min(\$valueone,\$valuetwo,\$valuethree,\$valuefour,\$valuefive,\$valuesix,\$valueseven,\$valueeight,\$valuenine,\$valueten,\$valueeleven,\$valuetwelve,\$valuethirteen,\$valuefourteen,\$valuefifteen,\$valuesixteen,\$valueseventeen,\$valueeighteen,\$valuenineteen,\$valuetwenty);

var text Svaluelist = SearchAndReplace(ToText(GetCellValues(GetRecords("({8.EX.'" & [Related Project] & "'} AND {51.XEX.'" & null & "'} AND {51.XEX.'" & 0 & "'} AND {51.XEX.'" & \$lowest & "'})"),51)),"\$","");

var number Svalueone = ToNumber(Part(\$Svaluelist, 1, ";"));
var number Svaluetwo = ToNumber(Part(\$Svaluelist, 2, ";"));
var number Svaluethree = ToNumber(Part(\$Svaluelist, 3, ";"));
var number Svaluefour = ToNumber(Part(\$Svaluelist, 4, ";"));
var number Svaluefive = ToNumber(Part(\$Svaluelist, 5, ";"));
var number Svaluesix = ToNumber(Part(\$Svaluelist, 6, ";"));
var number Svalueseven = ToNumber(Part(\$Svaluelist, 7, ";"));
var number Svalueeight = ToNumber(Part(\$Svaluelist, 8, ";"));
var number Svaluenine = ToNumber(Part(\$Svaluelist, 9, ";"));
var number Svalueten = ToNumber(Part(\$Svaluelist, 10, ";"));
var number Svalueeleven = ToNumber(Part(\$Svaluelist, 11, ";"));
var number Svaluetwelve = ToNumber(Part(\$Svaluelist, 12, ";"));
var number Svaluethirteen = ToNumber(Part(\$Svaluelist, 13, ";"));
var number Svaluefourteen = ToNumber(Part(\$Svaluelist, 14, ";"));
var number Svaluefifteen = ToNumber(Part(\$Svaluelist, 15, ";"));
var number Svaluesixteen = ToNumber(Part(\$Svaluelist, 16, ";"));
var number Svalueseventeen = ToNumber(Part(\$Svaluelist, 17, ";"));
var number Svalueeighteen = ToNumber(Part(\$Svaluelist, 18, ";"));
var number Svaluenineteen = ToNumber(Part(\$Svaluelist, 19, ";"));

var number secondlowest = Min(\$Svalueone,\$Svaluetwo,\$Svaluethree,\$Svaluefour,\$Svaluefive,\$Svaluesix,\$Svalueseven,\$Svalueeight,\$Svaluenine,\$Svalueten,\$Svalueeleven,\$Svaluetwelve,\$Svaluethirteen,\$Svaluefourteen,\$Svaluefifteen,\$Svaluesixteen,\$Svalueseventeen,\$Svalueeighteen,\$Svaluenineteen);

var text Tvaluelist = SearchAndReplace(ToText(GetCellValues(GetRecords("({8.EX.'" & [Related Project] & "'} AND {51.XEX.'" & null & "'} AND {51.XEX.'" & 0 & "'} AND {51.XEX.'" & \$lowest & "'} AND {51.XEX.'" & \$secondlowest & "'})"),51)),"\$","");

var number Tvalueone = ToNumber(Part(\$Tvaluelist, 1, ";"));
var number Tvaluetwo = ToNumber(Part(\$Tvaluelist, 2, ";"));
var number Tvaluethree = ToNumber(Part(\$Tvaluelist, 3, ";"));
var number Tvaluefour = ToNumber(Part(\$Tvaluelist, 4, ";"));
var number Tvaluefive = ToNumber(Part(\$Tvaluelist, 5, ";"));
var number Tvaluesix = ToNumber(Part(\$Tvaluelist, 6, ";"));
var number Tvalueseven = ToNumber(Part(\$Tvaluelist, 7, ";"));
var number Tvalueeight = ToNumber(Part(\$Tvaluelist, 8, ";"));
var number Tvaluenine = ToNumber(Part(\$Tvaluelist, 9, ";"));
var number Tvalueten = ToNumber(Part(\$Tvaluelist, 10, ";"));
var number Tvalueeleven = ToNumber(Part(\$Tvaluelist, 11, ";"));
var number Tvaluetwelve = ToNumber(Part(\$Tvaluelist, 12, ";"));
var number Tvaluethirteen = ToNumber(Part(\$Tvaluelist, 13, ";"));
var number Tvaluefourteen = ToNumber(Part(\$Tvaluelist, 14, ";"));
var number Tvaluefifteen = ToNumber(Part(\$Tvaluelist, 15, ";"));
var number Tvaluesixteen = ToNumber(Part(\$Tvaluelist, 16, ";"));
var number Tvalueseventeen = ToNumber(Part(\$Tvaluelist, 17, ";"));
var number Tvalueeighteen = ToNumber(Part(\$Tvaluelist, 18, ";"));

var number thirdlowest = Min(\$Tvalueone,\$Tvaluetwo,\$Tvaluethree,\$Tvaluefour,\$Tvaluefive,\$Tvaluesix,\$Tvalueseven,\$Tvalueeight,\$Tvaluenine,\$Tvalueten,\$Tvalueeleven,\$Tvaluetwelve,\$Tvaluethirteen,\$Tvaluefourteen,\$Tvaluefifteen,\$Tvaluesixteen,\$Tvalueseventeen,\$Tvalueeighteen);

If(_fid_51=\$lowest,1,
_fid_51=\$secondlowest,2,
_fid_51=\$thirdlowest,3)

------------------------------
Nathan Allan
------------------------------