1 Reply Latest reply on Jan 22, 2016 7:23 PM by Adam Crahen

    Return Nth Largest Number Across Numbers (Unsorted) for Each Row

    Eric Chio

      I've seen a couple of useful suggestions in this forum for returning the Nth largest number, but unfortunately all of them require the numbers to be sorted.  Is there a solution that would do the same with unsorted numbers?  Please see examples below:  I'm trying to find the 2nd largest score for each student among the test scores they got from the different exams.  The subjects have to be displayed in this particular order so numbers may not be sorted (at all) for all the rows.

       

      Student               Math          Science          History          English             Correct Answer

      student1              90               80                    95               70                         90

      student2              70               80                    75               95                         80

      student3              65               70                    80               100                       80

      student4              90               70                    70                65                        70

      student5              90               90                    85                85                        85

       

      If anyone has any ideas please let me know.

      -Eric

        • 1. Re: Return Nth Largest Number Across Numbers (Unsorted) for Each Row
          Adam Crahen

          Eric-

           

          Here is an idea.

           

          I would pivot the subject measures.  Go to Edit Data Source and select the subjects, right click on the header and select pivot.

           

          2016-01-22_21-40-28.png

          This will create two new fields called pivot field values and pivot field names.  I renamed these score and subject, respectively.

           

          Now create a field to rank the score, we are now looking at a single measure thanks to the pivot.

          RANK_DENSE(SUM([Score]))

           

          You can use this rank to find your value.  In the attached workbook, I created a parameter to emphasize score ranks anywhere from 1-4.  So with the parameter set to 2, you can see the 2nd highest score is orange.

           

          2016-01-22_22-22-33.png

          9.2 workbook attached.