4 Replies Latest reply on Nov 8, 2011 8:22 AM by philmodjunk

    calculate Age order by group

    HelderSantos

      Title

      calculate Age order by group

      Post

      I would like to build a script to let me know the age of my customers by groupping:

       (Group A) all <= 18
       (Group B) from 19 to 29
       (Group C) from 30 to 39
       (Group D) from 40 up

      i have table "person" (p_iD, p_name, p_Bdate, calc_age)

        • 1. Re: calculate Age order by group
          philmodjunk

          Case ( calc_age ≤ 18 ; "Group A" ;
                    calc_age ≤ 29 ; "Group B" ;
                    calc_age ≤ 39 ; "Group C" ;
                    "Group D"
                  )

          • 2. Re: calculate Age order by group
            HelderSantos

            Fantastic..... Thanks a lot! Just what i needed!!

            • 3. Re: calculate Age order by group
              HelderSantos

              Dear PhilModJunk,

              using "Case" it is possible to count in each group, how many people per group we have?

              • 4. Re: calculate Age order by group
                philmodjunk

                That depends on the structure of your database. I'd use another approach.

                If you sort your records by the value returned by our Case function here, we can use a summary field with the "count of" option to count the records in each group. You'd do so by adding a sub summary part "when sorted by" this calculation field to your layout and then you put the summary field inside this sub summary part. As long as you sort your records by the Age group calculation field, you'll see the counts for each group of records. You can include the list of individual records in the body of your layout or you can remove the body of the layout and just list the age groups with their counts on this layout.

                It's also possible to use a "self join" a relationship that links a table to itself, linking by this age group field and then the same summary field or a calculation field with Count ( RelatedTableOccurrence::SomeNeverEmptyField ) can be used to count the records in each age group.

                A secondary table with one record for each age group can also be used with a relationship that matches by age group to count records using the same summary field or Calculation field with Count ( ) to count the records in each group.