1 2 Previous Next 27 Replies Latest reply on Apr 11, 2017 10:55 AM by philmodjunk

    Identify top 35% of each class

    thereddiespirit

      FileMaker Community:

       

      I would like to identify students that meet the following criteria:

       

      ·        A major or minor in a psychology

      ·       Completed at least 3 semesters or full-time college coursework

      ·       Completed 9 semester hours of psychology

      ·       Overall GPA in top 35% of their class (based on rankings within sophomore, junior, & senior classes) compared to their classmates across the entire university

      ·       Minimum 3.0 GPA average for all psychology courses

       

      I'm stumped on finding the 35% or above for each classification of student (Senior, Junior, Sophomore) I have a layout that has seniors in GPA descending order, and have get(FoundCount) in a calculation field and identified the total number of seniors BUT do not know how to script to get the top 35%.

       

      Thanks in advance -

       

      jc

        • 1. Re: Identify top 35% of each class
          philmodjunk

          Don't use a find for that last refinement. (you can't as there's no value in the record to use with find criteria).

           

          What you might do is start with all records for a class and sorted and use Omit Multiple records to omit all but those in the top 35%. Then go into find mode, specify the remaining criteria and constrain the found set.

           

          The omit process would look like this:

           

          Go to Record/Request/Page [ 0.35 * Get ( FoundCount ) + 1 ]

          Omit Multiple Records [ Get ( FoundCount ) ]

           

          In the second step, any number greater than or equal to the total number of records from the current record to the end of the found set will work to omit all records save those that precede it. Get ( FoundCOunt ) is a convenient value that will always be more than the number to be omitted.

           

          Other options are also possible--you might use a relationship or ExecuteSQL to compute whether a given record is in the top 35% and then you have a value, though unstored, that you can use in a find.

          • 2. Re: Identify top 35% of each class
            siplus

            Let's start with a paradox: what if all students score the same results. Overall.

             

            In that case you would select only 35% of the records, while all are worth being chosen.

             

            IMHO you need to calculate a score involving all your parameters, calculate what 65% of that is worth, then find ALL records that have a score > that value.

            • 3. Re: Identify top 35% of each class
              philmodjunk

              An excellent observation. To put it in other terms, if there is a "tie" between multiple students at the cut-off point, what I have suggested might incorrectly exclude some students.

              • 4. Re: Identify top 35% of each class
                thereddiespirit

                This is good stuff! The omit is a great idea!

                 

                Now after the 35% are identified, and there are several students with the same GPA as the last individual in the 35%, how do I include them?

                For example out of 100 students:

                 

                Rank          GPA    

                34               3.1259

                35               3.055

                36               3.055

                37               3.055

                38               2.991

                 

                I want to include the 36th and 37th individual in the top 35%.

                 

                jc

                • 5. Re: Identify top 35% of each class
                  philmodjunk

                  That's exactly the point that siplus was making and with which I indicated agreement.

                   

                  What you could do, is capture the score at the 35% point in the found set  by setting a variable to this value and find all records with a score that is this value or better. This will then include all such "ties at the boundary" and is also find criteria that you can include with the other criteria used in your find.

                  • 6. Re: Identify top 35% of each class
                    thereddiespirit

                    Yes ! Agreed.

                     

                    How do I do that? Get that GPA value?

                    • 7. Re: Identify top 35% of each class
                      philmodjunk

                      Find the records

                      Sort them

                      Go to Record/Request/Page [ 0.35 * Get ( FoundCount ) ]

                      Set Variable [ $GPA ; value: YourTable::GPA FIeld ]

                      1 of 1 people found this helpful
                      • 8. Re: Identify top 35% of each class
                        thereddiespirit

                        This did it !!

                         

                        philmodjunk, if you are ever in the 71998 zip code, I'll buy you lunch!

                         

                        Thanks FileMaker Community for the help!

                         

                        jc

                        • 9. Re: Identify top 35% of each class
                          keywords

                          This is the same paradox the Olympic Games IOC needs to have pointed out to them. If there is a tie for third place, they award two (or more presumably if needed) Bronze Medals. But at Rio there was one event where there was a tie for first place. What did they do? Awarded two Gold medals, NO Silver medal, and one Bronze medal. Presumable their logic is that the medals go to the first three competitors, but this breaks down as soon as the third best performance is matched by others then they get recognised, but if there are tied scores amongst the top three they don't. They could resolve it all by awarding medals in order to the three best scores. That way, in the Rio event the third across the line would move up to the Silver medal and the next competition, who has record the third best score, would receive the Bronze.

                          A bit off topic I know. But vaguely relevant perhaps.

                          • 10. Re: Identify top 35% of each class
                            bertrand

                            As says Siplus, "Go to Record/Request/Page [ 0.35 * Get ( FoundCount ) ]" will select only the first 35 student of a list of 100 per example. If the 36th till the 50th have the same score than the 35th in the sort list they will be forgotten.

                             

                            It is righter to calculate which score accounts for 35% of the highest score, sort and take those which are above this value. In the same example, perhaps you will select 50 students, more than 35 students,

                            • 11. Re: Identify top 35% of each class
                              fmpdude

                              Shouldn't a rank be the same for a given (same) GPA?

                               

                              To wit:

                               

                              Rank          GPA

                              35              3.055

                              36              3.055

                              37              3.055

                               

                              Might be better calculated/stored like this:

                               

                              Rank          GPA

                              35              3.055

                              35              3.055

                              35              3.055

                               

                              My assumption is that the rank is computed from the GPA. Thus, two (or more) equal GPAs would have the same rank.

                               

                              So, assuming you calculate rank from GPAs like the second listing above, you could capture the to 35, based on rank, this way:

                               

                              Select * from <table_name> where rank <= 35

                               

                              (Or, using my test data, ExecuteSQL("Select rank from Top35 where rank<=35"; "";""))

                               

                              (I did not do the corresponding GPAs and rank calculations for this quick example.)

                               

                              Students - top 35% rank (inclusive)

                              1

                              2

                              3

                              4

                              5

                              6

                              7

                              8

                              9

                              10

                              11

                              12

                              13

                              14

                              15

                              16

                              17

                              18

                              19

                              20

                              21

                              22

                              23

                              24

                              25

                              26

                              27

                              28

                              29

                              35

                              35

                              35

                              35

                              35

                              35

                              35

                               

                              ------------

                               

                              Or, you could use a simple Find command to do the same thing:

                               

                               

                              ----

                               

                              Now, using an actual example...

                              Say you have the following sorted GPA data:

                               

                              gpa 

                              3.055
                              3.055
                              3
                              2.55
                              2.55
                              2.1
                              2
                              2
                              1.1
                              1.1

                               

                              I might calculate the ranks using a simple script (no comments, lightly tested) like this:

                              Which gives the following display:

                               

                              Then, apply the SQL (or Find) to get the top "35%" of these.

                               

                              (This link may also have useful background info or info for testing, etc.: Excel RANK Function Examples )

                               

                              -----

                               

                              I've attached a larger (1,000 student) randomly-generated CSV sample file using the same approach.

                               

                              The top 10 values shown below:

                               

                              gpa,rank

                              "4","1"

                              "4","1"

                              "3.99","2"

                              "3.99","2"

                              "3.97","3"

                              "3.97","3"

                              "3.97","3"

                              "3.96","4"

                              "3.96","4"

                              "3.96","4"
                              .

                              .

                              .

                               

                              -----

                               

                              FWIW, In MySQL, you can calculate Ranks in a single SQL statement, like this (other enhancements also possible):

                              1 of 1 people found this helpful
                              • 12. Re: Identify top 35% of each class
                                rgordon

                                Class ranking should look like this

                                1

                                2

                                2

                                4

                                4

                                6

                                7

                                7

                                7

                                10

                                 

                                not

                                 

                                1

                                2

                                2

                                3

                                3

                                4

                                5

                                5

                                5

                                6

                                 

                                In the above example if you have a parent conference for the student with the lowest gpa you would not tell the parent that the student ranked 6th out of 10 students.  His rank was 10th out of 10 students.  He might have had the 6th highest gpa but gpa and rank are not the same thing.  Another way of looking at this is if a student has a ranking of 6 this would mean there are 5 students above his ranking. In the above example, there are only 6 students with a higher gpa than each of the students tied for 7th.

                                • 13. Re: Identify top 35% of each class
                                  rgordon

                                  Here is a sample file that ranks each class and is flexible in allowing you to change the 35% criteria to whatever percent you want.  No students will be excluded in the event of a tie so you may end up with more than the percent you enter.

                                  • 14. Re: Identify top 35% of each class
                                    philmodjunk

                                    I'm wondering if you actually read my last post. The OP does not want to rank the students.

                                     

                                    The method I outlined finds the GPA at 35% and then uses that to find all students with that GPA or better. Thus, tie scores are handled.

                                     

                                    But it maybe you all are still discussing the Olympic rankings ;-)

                                    1 2 Previous Next