6 Replies Latest reply on Apr 3, 2013 6:25 AM by jon.baker

    Rank of cumulative score

    jon.baker

      Hi,

       

      I'm trying to show a rank of cumulative score for a competition over time.

       

      Competitors score points for each time point, and I want to be able to show the rank of a competitor at each timing point based on their cumulative score up to that point.

       

      So far, I can only figure out how to show (1) Score at each time point; (2) Cumulative score at each time point; (3) Rank of score at each time point [i.e. rank of (1)]. What I need to do is to show the rank of cumulative score at each time point (2).

       

      How do I show the rank of the cumulative score instead?

       

      Screen shot attached... I need to show the rank of the scores circled in blue.

       

      screenshot2.png

       

       

      Thanks.

      Jon

        • 1. Re: Rank of cumulative score
          Tracy Rodgers

          Hi Jon,

           

          So do you want to rank the Competitors in order 1-5? Would it be possible to post a packaged workbook (twbx file) with the described outcome?

           

          -Tracy

          • 2. Re: Rank of cumulative score
            jon.baker

            Hi,

             

            I want to rank the competitors in order 1-5 at each time point, based on their cumulative score at that point. Here's what I want the outcome to look like (done in Excel).

             

            screenshot3.png

             

            The file is saved on tableau public here (let me know if the link doesn't work): http://public.tableausoftware.com/download/workbooks/Cumulative_Score_and_rank?format=html

             

            NB: That's a simplified version of my real data, I realise I could just put a cumulative score in my source data in this case, but I'm going to need to replicate it over a large number of fields.

             

            Thanks,

            Jon

            • 3. Re: Rank of cumulative score
              Tracy Rodgers

              Hi Jon,

               

              To get the desired outcome, first create a calculated field for the running sum of points:

               

              RUNNING_SUM(SUM([Points]))

               

              Then, create a calculated field similar to the following to get the desired rank:

               

              IIF([Running Sum of Points] == LOOKUP([Running Sum of Points], -1), PREVIOUS_VALUE(1), INDEX(), 1)

               

              Then, using the same compute using logic as you have on Rank_Cumulative.

               

              Hope this helps!

               

              -Tracy

              1 of 1 people found this helpful
              • 4. Re: Rank of cumulative score
                jon.baker

                Thanks Tracy.

                 

                I've put in the fields as you said, but I'm still not able to get it to give me the result I need. I think because the ranking of the competitors changes over time, but the order that they are displayed in in the table doesn't change, the index() isn't always giving the correct answer.

                 

                What I need to do is calculate the ranking by sorting on the running_sum calculated field, but it's not showing in the dropdown list when I go to edit calculation > advanced > sort.

                 

                How do I sort by the calculated running sum field?

                 

                Updated file is here: http://public.tableausoftware.com/views/Cumulative_Score_and_rank/Sheet1?:embed=y&:display_count=n

                 

                Thanks again.

                Jon

                • 5. Re: Rank of cumulative score
                  stryder dsf

                  Hi Jon,

                  I had to do something of the same sort.

                  First you need to create as many fields as the no of times you need the cumulative score.I hope you would have done that

                  You need to create calculated field for each rank defines as"index()" and add it to ROWS in the worksheet table.Now click on the calculated field and go to "Edit table calculation"

                  Use compute using advanced and then a new window appears.select all names in the partitioning box  and transfer them to addressing

                  Now use sort by field and select the calculated field according to which you want to sort. then select if its to be ascending or descending.

                  I think that should do it.However if filter the data of only the top 15 or so,then ranks will be calculated amoing the 15 only for all fields.

                  Hope this helps.

                   

                  Abhishek Ranjan,

                  Trisys.in

                  1 of 1 people found this helpful
                  • 6. Re: Rank of cumulative score
                    jon.baker

                    Hi,

                     

                    Can anyone explain to me how I do this? I just can't get my index() function to be sorted based on the running_total field.

                     

                    Am I missing something really obvious?

                     

                    Thanks,

                    Jon