10 Replies Latest reply on May 18, 2010 12:29 PM by Woodzie

    Sub Summaries for Gender

    Woodzie

      Title

      Sub Summaries for Gender

      Post

       

      I have run into a difficulty in reporting the number of police officers by gender on each peacekeeping mission. I'm working with three related tables Personnel Records (holds gender info) , a Missions Table (holds mission info) and Experience (join table holding relative experience info)

       

      I cannot seem to be able to count officers listed as female / male in sub summaries.

       

      Ideally, the report will  look like this

       

      AMISOM                **This is my first sub summary by Mission Acronym** No problem.

      Male: 1                  **This is my second summary by male. I've tried several pieces of code for doing this...none have worked latest is

      Female: 3  <<male>> <count of people>>** -- male code is ValueCount ( FilterValues ( List ( Gender ) ; "Male" ) )

      Total: 4

       

      UNAMID

      Male: 4

      Female: 3

      Total: 7

       

      Grand Total: 11

       

      I know I'm  missing something but I can't figure it out...probably simple as I haven't located the solution on the forums. Thanks in advance for the help

       

      w.

       

       

        • 1. Re: Sub Summaries for Gender
          mrvodka

          No there whould be 2 subsummary parts. The first one grouped by (break field ) Mission Name and the second by gender.

           

          Do not forget to sort by both break fields.

          • 2. Re: Sub Summaries for Gender
            comment_1

            You need a summary field, defined (in the Experience table) as Count of [any field that cannot be empty].

             

            Place this same field in both sub-summary parts and in the grand summary part.

            • 3. Re: Sub Summaries for Gender
              Woodzie

              Thanks! For whatever reason, this didn't work the first time I tried it and I didn't debug properly. Cheers Mr. Vodka.

              • 4. Re: Sub Summaries for Gender
                Woodzie

                I have to say this forum has been invaluable to me. I'm almost finished my project and have only a couple more issues to solve. 

                 

                This report must be expanded to include a count of the expected return dates (month and year only).  I can't seem to wrap my head around the script that would provide this for me.

                 

                An approximation of the report would be this

                 

                UNAMID *sub summary*

                Male:5

                Female: 8

                 

                Expected return dates

                June 2010 - 3

                August 2010 - 4

                January 2011 - 6

                 

                AMIS*sub summary*

                Male:5

                Female: 8

                 

                Expected return dates

                June 2010 - 3

                August 2010 - 4

                January 2011 - 6

                 

                1. I'm unsure how to produce the proper counts and

                2. Would I make use of a portal to bring in the report?

                 

                Thanks again all,

                 

                w.

                • 5. Re: Sub Summaries for Gender
                  comment_1

                  This is not possible with a summary report. Filemaker cannot summarize the same data twice, in two different orders.

                  You can have either:

                  UNAMID
                  Male: 5
                  • June 2010: 2
                  • August 2010: 1
                  • January 2011: 1
                  Female: 8
                  • June 2010: 1
                  • August 2010: 2
                  • January 2011: 5

                  or:

                  UNAMID
                  June 2010: 3
                  • Male: 1
                  • Female: 2
                  August 2010: 4
                  • Male: 3
                  • Female: 1
                  January 2011: 6
                  • Male: 1
                  • Female: 5














                  • 6. Re: Sub Summaries for Gender
                    Woodzie

                    Thanks! Problem one solved, as that layout will do.

                     

                    Problem two...I cannot seem to summarize by month in a field as you have shown (i.e. group all January 2011 returnees together).  How do I call and total only the months.

                     

                    It's probably something basic, I just can't see it.

                     

                    Jeff

                    • 7. Re: Sub Summaries for Gender
                      comment_1

                      You need a calculation field that will return a common value for all records in the same month, e.g. =

                       

                      ReturnDate - Day ( ReturnDate ) + 1

                       

                      Use this field as the breakfield for the sub-summary by month part.

                      • 8. Re: Sub Summaries for Gender
                        Woodzie

                        Hi Comment, thanks for the assistance. The month calculation I'm using works great

                         

                        MonthName(Date of Expected Return - Month (Date of Expected Return)  + 1)

                         

                        However, as deployments last several years, I need to add the year to each one as well.

                         

                        After experimenting with GetAsDate functions and expanding the current calculation I can't seem to make it work.

                         

                        Any suggestions?

                        • 9. Re: Sub Summaries for Gender
                          comment_1

                           


                          Woodzie wrote:

                          The month calculation I'm using works great

                           

                          MonthName(Date of Expected Return - Month (Date of Expected Return)  + 1)


                           

                          No, I am afraid It doesn't work at all. It makes no sense to subtract the month from a date. Your formula will return "February" when the expected return date is March 1. And of course, using MonthName takes the year out of the equation. And the result cannot be easily sorted in the correct order.

                           

                          Why don't you use the suggested calculation and. if you want to display the month name, custom format the field to display only the month name (or the month name and the year).


                          • 10. Re: Sub Summaries for Gender
                            Woodzie

                            Good grief...looks like I picked a bad day to quit coffee...

                             

                            Report is all good now. As a novice, there are just so many settings to keep track of...but I finally got them all correct. And, I' getting the hang of the logic.

                             

                            Thanks again from a noob.

                             

                            w.