Forum Discussion

NathanAllan3's avatar
NathanAllan3
Qrew Trainee
4 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
------------------------------
Formulas and functions
  • NathanAllan3's avatar
    NathanAllan3
    Qrew Trainee
    4 years ago
    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
    ------------------------------