5 Replies Latest reply on Dec 7, 2009 7:05 AM by DrDave

    Frequency Count



      Frequency Count


      I have a database with photography information, such as PhotoTitle and PhotoScore (Score is between 15 and 30).


      I want to get a frequency count of each score, such as:


      Score   Count


      15         35

      16         27

      17         12


      What's the best way to achieve this? All feedback is appreciated.... 

        • 1. Re: Frequency Count
             A qucik subsummary report grouped by score would give you this. Use a summary field with count option. Dont forget to sort by the break field ( score ). You can delete out the body part if you dont want the breakdown details.
          • 2. Re: Frequency Count

            So you have 2 fields.


            Now create another one ( AllScores ) type Summary = Count of Score


            Define your count field as a calculation with this calc:


            GetSummary ( AllScores ; Score )


            The needed result could be see ONLY if you'll sort by Score.

            • 3. Re: Frequency Count

              Thank you Ray for offering this detailed advice. I'm not sure that I fully understand it.


              I created a field call AllScores and as you suggested, made it a Summary field, using the "Count of Score" (Actually it's called TotalScore)


              Are you also asking me to create another field, called "Count" or "FreqCount"? That's what I did, I created a field called FreqCount and used the calculation that you recommended: GetSummary(AllScores ; TotalScore)


              I sorted the database by TotalScore.


              What do I do next?


              Thank you for your help.... 

              • 4. Re: Frequency Count



                Thank you for your posts.


                The solutions by mr_vodka and raybaudi are correct (Thank you!).  Since you have a little confusion, let's expand a bit more on their answers.


                In your example, assume the score is stored in the aptly named field "Score".  To get a count of these scores, create a Summary field "TotalScore" which is a Count of Score.  From your post, it sounds like you did this without any problem.  However, when you return to Browse Mode, you will see a count of all records; not for each individual Score.


                To view each individual score, let's first build a report...


                1. Pull down the View menu and select Layout Mode.

                2. Pull down the Layouts menu and select New Layout/Report...

                3. Under Select a layout type, select "List/Report" and click "Next"

                4. Although we want a report with Grouped data, select the default option of "Columnar list/report" and click "Next"

                5. Move the fields "Score" and "TotalScore" from the Available fields on the left to the Layout fields on the right.  Click "Next".

                6. For Sort records, move over the field "Score".  Click "Next". 

                7. For Select a Theme, select either Default or Standard and click "Next".

                8. Click "Next"

                9. ... and do not create a script, so click "Next" again.

                10. Click "Finish".


                You are now put into Browse Mode, and your layout doesn't look like anything you want.  Stay with me.  Pull down the View menu and select Layout Mode.  On the left side of the layout, you will see Header, Body, and possibly Footer.  Double-click on "Body" and a Part Definition dialog box displays.  Change the radio button from "Body" to "Sub-Summary when sorted by", and on the right side, highlight "Score".  Click "OK", and you are then prompted for if you want the part to print above or below the records that are summarized.  Although it doesn't matter in this case, choose Below.  Pull down the View menu and select "Browse Mode".  You should now see each Score with the appropriate counts.


                Now, if you want to see this information in each record outside of this report, then you'll need to create a new field.


                1. Pull down the File menu and select "Manage -> Database...".


                2. Create a new field, FreqCount, as a Calculation.  The formula would be:


                GetSummary ( TotalScore ; Score )


                This calculation will only work when you sort by Score.  If the table is not sorted, then no value is displayed.


                Now, using your original example, if you go to a record where the Score was 15, then FreqCount will display 35.  If you go to a record where the Score is 16, then FreqCount displays 27.


                Please let me know if you need clarification for any of the above steps.



                FileMaker, Inc. 

                • 5. Re: Frequency Count

                  Awesome response.


                  Please give me the home address of your Boss -- I want to go to his house and tell him that you need a raise. ;-)


                  Thank you.