5 Replies Latest reply on Apr 12, 2018 6:29 AM by Jim Dehner

    How To Calculate the Average Test Score by Tercile

    Edd Friedman

      I have a  data source with the following fields:

      • schoolYear - an integer representing the year of the last day of school for any given year
      • testID - the unique identifier for each test
      • testGrade - the grade level for a given testID (Kindergarten, First Grade, Second Grade, etc)
      • testWindow - 1 for Fall administration, 2 for Winter administration, 3 for Spring administration
      • studentID - the unique identifier for a each student
      • studentScore - the score the student achieved on a given administration of a given test

       

      I would like to calculate the average student score for the upper, middle, and lower terciles (thirds) for each testWindow of each testID, of each schoolYear. I can't seem to wrap my head around how to accomplish this.

       

      It's easy to calculate the overall average, but I can't figure out how to divide the scores into terciles to get the average for each tercile. Any help is greatly appreciated.

        • 1. Re: How To Calculate the Average Test Score by Tercile
          Jim Dehner

          Hi Edd

          This problem requires several steps and without your workbook it would be difficult to explain

          Last week I did something similar for some one who wanted to look at sales in quintile buckets - by State (US), category and year

           

          I attached a T10.2 workbook for that solution - the general approach it to determine the boundary values between groups using the Percentile function

          Then assigning records into groups based on those boundaries and then using nested LOD's expressions that support varying the groups and year while maintaining buckets at the state level

          For presentation purposes I used a map of the US with years on pages to provide a dynamic view of how the sales quints changed with time

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: How To Calculate the Average Test Score by Tercile
            Edd Friedman

            Jim,

             

            THANK YOU! I think this is what I need. I took a quick look at your workbook and realized that part of my problem was that I was trying to use RANK_PERCENTILE() instead of PERCENTILE() which resulted in an error about mixing aggregate and table calculations.

             

            I will investigate further tonight and let you know how it works out.

             

            THANKS AGAIN!

            • 3. Re: How To Calculate the Average Test Score by Tercile
              Jim Dehner

              Thanks

              Percentiles are one of the most misunderstood functions in Tableau (I might write a paper on that)

              I used the model I sent you in a presentation last night -

              after going through it I think they understood how to put it together

              If you have any questions let me know

               

              Jim

              • 4. Re: How To Calculate the Average Test Score by Tercile
                Edd Friedman

                Jim,

                 

                I have attached a version of my workbook. I could not figure out how to apply LOD to the calculation of the Tercile values. However, I believe that I was successful in calculating them correctly without LOD expressions.

                 

                Now I cannot figure out how to calculate the average of the scores within each tercile. Any help is greatly appreciated.

                 

                -Edd

                • 5. Re: How To Calculate the Average Test Score by Tercile
                  Jim Dehner

                  Hi Edd

                   

                  see the attached

                  There are a lot of moving parts in your book and I am not versed in what you are actually goals -

                  I understand that you want to count the number of something in terciles but not clear what that is

                  1. number of students by year/test grade /test window based on AVG score
                  2. number of students by year/test grade /test window based on min score
                  3. number of scores by year/test grade /test window
                  4. a straight record count in each group
                  5. or maybe something else

                   

                  In your analysis you will want to determine the boundary point between the terciles based on that combination of dimension AND fix that number for comparing the actual measure to count how many fit in each group

                   

                  That is where the LOD's come in -

                  there are 2 sheet (red tabes) in the workbook the first sheet looks like this

                   

                  the first is the T1 calculation from your book - not an LOD

                  The second uses an lod to include the dimensions on your columns shelf

                  and the third nest the LOD form 2 to fix the result

                   

                  In this view they all return the same values leaving the impression that you could use any of them to count the members in each group

                   

                   

                  But on the second tab I added Person ID - assuming that you wanted to put people into the groups and then do a count

                   

                  both the first and second calculation above will vary by person - so are not a boundary that can be used to group people -

                  only the third - nested LOD is a fixed boundary that can be used for grouping

                   

                   

                  OK so what - the next step is to group something into the 3 pieces created by the boundaries but

                  I don't know enough about the goals her to first assure we are looking at the right level and second know what to place in each group and count

                   

                  Does that make any sense?

                   

                  JIm