2 Replies Latest reply on Apr 2, 2014 9:39 AM by gremlin9297

    Calculation of number of members within an age range



      Calculation of number of members within an age range


           Hi there,

           Okay so I'm VERY new to filemaker and still learning the ins and outs of it. I'm trying to create a report based off members age ranges.. 0-25, 25-30, 30-35, etc... I've been searching forums and help sites but nothing that offers any clues or hints about creating a calculation to calculate a range. Sorry if this sounds confusing, just need some help with finding a calculation of specific range that isn't dates. If anyone has any clue how to help or even slightly gets where I'm going with this please let me know. I'll try to clear up anything that doesn't make sense as best I can. 

           Thank you!

        • 1. Re: Calculation of number of members within an age range

               And what do you want that report to look like?

               Do you want something like this?

               0-25   10 members
               25-30  23 members
               30-35  42 members

               and so forth?

               There are multiple approaches possible, but they are more than just a calculation, how you design your layout and how this data is recorded in tables in your database make a big difference.

               To get the above list, here are two approaches that can work:

               1) Use a summary report.

               Define a calculation field that will return the same value for all members in the same age group. Define cAgeGroup like this:

               Case ( Age < 26 ; 0 ; // all members 0 to 25
                           Age < 31 ; 1 ; // all members 26 to 30
                           2   // all members older than 30 (you can continue this pattern to have more groups

               Select Number as this calculation field's result type.

               Now define a summary field as the "count of" Age (any field that is never empty can be selected and you get the same count.)

               Next, Create a layout based on your members table and remove the Body layout part. Replace it with a sub summary layout part "when sorted by" cAgeGroup. Put the summary field inside this sub summary layout part.

               Now enter Browse mode, use List view to view your layout and be sure to sort your records by cAgeGroup and you can get the report that I showed.

               Option 2: Use a related table of age groups.

               Define a related table with at least these two fields: ageGroup and cMemberCount

               Link it in a relationship like this:

               AgeGroups::AgeGroup = Members::cAgeGroup

               Then define cMemberCount as:

               Count ( members::Age ) // again, any field can be counted as long as it is never empty.

               Create one record for each age group with values in the AgeGroup field of 1, 2, 3 etc to match the values returned by cAgeGroup and a list or table view layout can show the same results as the summary report.

               You could also not use the cAgeGroup calculation field if you used this relationship:

               AgeGroups::minAge > Members::Age AND
               AgeGroups::MaxAge< Members::Age

               Note: One drawback to this example of a relationship based method is that Members::Age must be an indexed field. Since Age fields are often unstored (and thus unindexed) calculation fields that use the member's birthdate and get ( CurrentDate ) to compute the age, this requires changing how you keep the correct age up to date for each member such as a periodic script that checks for recent birthdays and then updates any affected Age fields.

          • 2. Re: Calculation of number of members within an age range

                 Thanks for the quick reply. I'll try it out today! Though that is basically what I was looking for. Sounds like summary report is the way to go and will accomplish my task.