Forum Discussion

ToddMolino's avatar
ToddMolino
Qrew Cadet
5 hours ago

Formula Query for Ranking

I'm creating competition between Agents by creating a leaderboard, however my formula query is giving me an issue. The ranking is based on the AFLAC Submitted Premium Last Week column, (FID 677). Image attached. Essentially, the highest dollar amount would land in spot 1 and continue with next highest in spot 2 etc. 

Here's what I have thus far: 

var text SUBLIFE = "{677.GT.'" & [AFLAC Submitted Premium Last Week] & "'}AND{23.EX.'1'";

Size(GetRecords($SUBLIFE)) + 1

Any help would be greatly appreciated! 

The attached report is sorted lowest to highest by Rank.

formula query

1 Reply

  • ToddMolino's avatar
    ToddMolino
    Qrew Cadet
    1 hour ago

    I was able to resolve this with further self inflicted mental torture. If anyone is interested, the correct query is below. This formula did not need the "+ 1" after the Size(Get Records query that was shown in multiple resources. 

    var text SUBAFLAC =

    "{677.GT." & [AFLAC Submitted Premium Last Week] & "}" &

    "AND{23.EX.1}" & 

    "AND{691.GT.0}";

     

    Size(GetRecords($SUBAFLAC))