2 Replies Latest reply on Apr 7, 2016 4:25 PM by Erwin Gutierrez

    Alternative Rank Function (effectively equals ranking)

    Erwin Gutierrez

      Hi Community,

       

      I'm wondering if there is a ranking algorithm in Tableau that will produce the ranks in my example below.

       

      ScoreHeader 2

      75

      1
      741*
      503
      493*
      493*
      484*
      32=6
      32=6

       

      *Denote ' effectively equal ranking' (only 1-point difference).

       

      Any help is greatly appreciated. Thanks

       

      Erwin

        • 1. Re: Alternative Rank Function (effectively equals ranking)

          Hey Erwin,

           

          Have you tried writing a calculated field using IF/THEN statements to categorize the ranks?

           

          IF [Score] >= 50 THEN "1" ELSEIF [score] >= 48 THEN "1*"

           

          My syntax may be off but the concept should be the same.

          • 2. Re: Alternative Rank Function (effectively equals ranking)
            Erwin Gutierrez

            Hi Diego,

             

            Thanks for the reply. I've played around with if/then statements and here's what I have come up with so far:

             

            //Condition that puts "*" for point scores with 1 point difference. Works for ONLY up to 24 scores (rows) in a table.

            IF
            [Score] == LOOKUP([Score], -1)-1 or [Score] == LOOKUP([Score], -2)-1 or [Score] == LOOKUP([Score], -3)-1 or [Score] == LOOKUP([Score], -4)-1 …[more lookup statements ]…..or [Score] == LOOKUP([Score], 23)-1
            Then
            str(RANK([Score],'desc')-1)+"*"
            //Condition that puts "=" if point scores are equal. Note SUM(Scores) == LOOKUP(SUM(Scores), 1) is used in case the 1st point score is equal to the 2nd.
            ELSEIF
            [Score] == LOOKUP([Score], -1) or [Score] == LOOKUP([Score], -2) or [Score] == LOOKUP([Score], -3) or [Score] == LOOKUP([Score], -4) …[more lookup statements ]…….or [Score] == LOOKUP([Score], 23)
            Then
            "="+str(RANK([Score],'desc'))
            ELSE
            str(RANK([Score],'desc'))
            END

             

            Unfortunately my code is a bit hard coded and will only work when ranking 24 scores against each other. If you have an idea of how to shorten my IF statements, so that it can work for as many scores as possible.

             

            Erwin