9 Replies Latest reply on Feb 28, 2017 11:37 PM by ienalpe28

    Create Ranking by Calculation

    ienalpe28

      Hi All

       

      Im a beginner in filemaker and I just want to ask how to get ranking of fields. I have 10 fields which is:

       

      Average1

      Average2

      Average3

      ...

      Average10

       

      Each of them has an average score. I want to get the top 5 from their score by putting it on another field calculation which is Top1, Top2... Top5.

      I got the Top1 by this calculation:

       

      Top1 = Max ( Average1, Average2, Average3 ... Average10 )

       

      But I have a difficulty in getting the Top2 ... Top5

       

      Here is an example:

       

      Average1 = 5.0

      Average2 = 3.2

      Average3 = 8.5

      Average4 = 6.5

      Average5 = 4.8

       

      Top1 should be 8.5

      Top2 should be 6.5

      Top3 should be 5.0

      Top4 should be 4.8

      Top5 should be 3.2

       

      Thanks

        • 1. Re: Create Ranking by Calculation
          jbrown

          Hello.

          The Max function is only going to return the top value. So we have to look elsewhere.

           

          What we need is something that will sort the values in descending order.  Do you have FIleMaker Pro Advanced? If so, you can use a Sort List Custom Function that will sort a list. Have you worked with Custom Functions before?

           

          I built a demo and put in a list of all values in the 10 fields:

          Let ([
          _theList =  List ( Avg1;Avg2;Avg3;Avg4;Avg5;Avg6;Avg7;Avg8;Avg9;Avg10);
          _SortedList = SortList ( _theList ; "Des"; "Number")
          

           

          Then I simply culled the top 5 off that list.

          I used the nice FileMaker function: Left Values, which takes a number of values from the left (top) of the list.

          In my calculated field, I have this complete calc:

           

          Let ([
          _theList = List ( Avg1;Avg2;Avg3;Avg4;Avg5;Avg6;Avg7;Avg8;Avg9;Avg10;
          _SortedList = SortList ( _theList ) ; "Des"; "Number")
          
          ];
          
          
          LeftValues ( _SortedList ; 5)
          )
          

           

          This does as expected: returning the top 5 values.

          Screen Shot 2017-02-19 at 8.28.19 PM.png

           

          ---

          The above works as you describe . . .

          However, you should give some thought to putting those averages into a child table, one new record for each score. That would be the 'proper' way to do it. Anytime you have fields with the suffix of 1, 2, 3 . . . . you find a strong clue to using related child records. I'll put together a small demo to illustrate this.

          • 2. Re: Create Ranking by Calculation
            jbrown

            IF you want one field per "TopAverage", then you'll have to do some work to cull from the list.

            Review the functions LeftValues and MiddleValues.

             

            Each TopAverageField would have the same calculation as my original example but use these functions to take one row, depending on the fieldName.

             

            So Top1 would be

            Let ([
            _theList =  List ( Avg1;Avg2;Avg3;Avg4;Avg5;Avg6;Avg7;Avg8;Avg9;Avg10);
            _SortedList = SortList ( _theList ; "Des"; "Number")
            
            ];
            
            LeftValues ( _SortedList ; 5)
            )
            

             

            Top2 would use the MiddleValue function in line 7

            Let ([
            _theList =  List ( Avg1;Avg2;Avg3;Avg4;Avg5;Avg6;Avg7;Avg8;Avg9;Avg10);
            _SortedList = SortList ( _theList ; "Des"; "Number")
            
            ];
            
            MiddleValues ( _SortedList ; 2; 1)
            )
            

             

            Top3 would be:

            Let ([
            _theList =  List ( Avg1;Avg2;Avg3;Avg4;Avg5;Avg6;Avg7;Avg8;Avg9;Avg10);
            _SortedList = SortList ( _theList ; "Des"; "Number")
            
            ];
            
            MiddleValues ( _SortedList ; 3; 1)
            )
            

             

            And so forth. Notice for Top2 and Top3, I'm using the middleValues, which asks me first for the list, then the starting row, and how many rows after that.

            • 3. Re: Create Ranking by Calculation
              philmodjunk

              It would be simpler to put each average in a separate related record. Then simple sorting can return the top values. This can be thru a sorted found set, sorted portal, sorted relationship or a sorted ExecuteSQL function call.

              • 4. Re: Create Ranking by Calculation
                jbrown

                See the attached pic. I've created a child table called Tests and have all the scores in there for a student in my example.

                There's a relationship that sorts them by the average score descending. And then I can pick the values that I need out of a calculated field called "Top5Scores"

                 

                As I mentioned above and Phil said is 'much more simple' I would recommend this approach. Again, any data in fields that end in a number is better put in a child table.

                 

                This approach is all FM; that custom function i mentioned is not needed.

                 

                See my example.

                • 5. Re: Create Ranking by Calculation
                  BruceRobertson

                  Where do these averages come from?

                  Did they originate from other FileMaker data?

                  It would be most helpful if you would provide a lot more description.

                  • 6. Re: Create Ranking by Calculation
                    ienalpe28

                    Hi jbrown thank you for your response. I have not tried custom function in Filemaker although I have a Filemaker Pro 14 Advanced. Sorry but I'm really a beginner. Can you please give me a step by step instruction on how to integrate this custom function. I did it myself but it shows "Error: Check sort and type". I would be grateful for your assistance.

                    • 7. Re: Create Ranking by Calculation
                      jbrown

                      I got this error first as well. Check out my demo file in a response below and look at the table called RankAverages.

                      • 8. Re: Create Ranking by Calculation
                        user19752

                        As they wrote, having 10 fields is not best design usually.

                         

                        I played with SQL...

                        You need "id" (unique non-null) field to determin current record in SQL.

                         

                        LeftValues(

                        ExecuteSQL("

                        SELECT average1 FROM sortinrec WHERE id=?

                        UNION ALL SELECT average2 FROM sortinrec WHERE id=?

                        UNION ALL SELECT average3 FROM sortinrec WHERE id=?

                        UNION ALL SELECT average4 FROM sortinrec WHERE id=?

                        UNION ALL SELECT average5 FROM sortinrec WHERE id=?

                        UNION ALL SELECT average6 FROM sortinrec WHERE id=?

                        UNION ALL SELECT average7 FROM sortinrec WHERE id=?

                        UNION ALL SELECT average8 FROM sortinrec WHERE id=?

                        UNION ALL SELECT average9 FROM sortinrec WHERE id=?

                        UNION ALL SELECT average10 FROM sortinrec WHERE id=?

                        ORDER BY 1 DESC

                        ";"";"";id;id;id;id;id;id;id;id;id;id)

                        ;5)

                        • 9. Re: Create Ranking by Calculation
                          ienalpe28

                          Hi sorry I was away for a few days. I just want to thank Jeremy Brown for giving the correct answer to my problem. Thanks buddy! If I can only buy you a drink I would. This project is for an appraisal system. My wife is doing it manually, the computation part is very tedious and consumes a lot of time when she is about to make her report. I want to surprise her by doing this solution so she can spend more time with me . Anyway thanks again guys for the help specially to Jeremy Brown. Thanks again buddy.