6 Replies Latest reply on Aug 9, 2010 8:27 AM by philmodjunk

    How to include the grand total in the subsummary calculation?

    FBrew

      Title

      How to include the grand total in the subsummary calculation?

      Post

      I'm trying to make a report that shows statistics.

      For example: There are 1,000 students. Im trying to include the Total (1,000) in the sub-summary when breaking down the students into categories. So the report should look like the following:

      State Students are from Number of students Ratio
      State A 200 20%
      State B 300 30%
      State C 500 50%
      Total 1,000 100%

      How do I get the Ratio column numbers?

      I've tried all sorts of stuff with the States groupings in subsummaries, but the Ratios would all be 100% each or nothing.

      Thanks for you help in advance.

        • 1. Re: How to include the grand total in the subsummary calculation?
          philmodjunk

          GetSummary ( TotalNumberofStudents ; StateField ) / TotalNumberofStudents

          Should work as long as your records are sorted by StateField.

          You should also be able to define a "fraction of total" summary field that gives you the same results.

          • 2. Re: How to include the grand total in the subsummary calculation?
            FBrew

            Thank you, PhilModJunk, for you quick reply.

            Getting the TotalNumberofStudents to be 1000, not the total of each subsummary seems to be the hard part that I'm stumped on.

            I should have mentioned that I tried GetSummary(), but I know how to get the TotalNumberofStudents value to be the grand total, not the sub-summary total.

            For example, i get the following, which is wrong:

            State Students are from Number of students Ratio TotalNumberofStudents (each line should be the total of 1,000, but isn't)
            State A 200 100% 200
            State  300 100% 300
            State  500 100% 500
            Total 1000 100% 1000

            I'm trying to get the following as below. If I can get the TotalNumberofStudents to be the actual total, I can make the ratio calculations for each subsummary.

            State Students are from Number of students Ratio TotalNumberofStudents (each line should be 1,000)
            State A 200 20% 1000
            State  300 30% 1000
            State  500 50% 1000
            Total 1000 100% 1000

            I've upgraded from FileMaker Pro 10.0v3 to FileMaker Pro 11 with the latest update on Mac OS X 10.6.4. And Have FileMaker Pro 11 Server on a Mac X 10.6.4 Server.

            • 3. Re: How to include the grand total in the subsummary calculation?
              philmodjunk

              The examples I suggested should get you the correct percentages. Did you try and see if that worked for you?

              To get the grand total in the last column, I'd use a relationship that matches any record in the table to all the records in the table (or whatever group of records represents your grand total). Then Sum ( relatedtableOccurrence::Number of students ) will return the total number.

              If you are matching all records in the table use Table::Anyfield X Table 2::anyfield

              where Table 2 is a second table occurrence of the same table. You can create this by dragging from a field in Table to outside the box and back to a field in Table. The self join relationship with new table occurrence will be created when you release the mouse.

              • 4. Re: How to include the grand total in the subsummary calculation?
                philmodjunk

                Come to think of it, just putting the name of the number of students summary field in a calculation field should return the grand total of the current found set for you and this would give you the last column of data without any of the extra steps I just posted.

                • 5. Re: How to include the grand total in the subsummary calculation?
                  FBrew

                  Come to think of it, just putting the name of the number of students summary field in a calculation field should return the grand total of the current found set for you and this would give you the last column of data without any of the extra steps I just posted.

                  That worked! Thank you, PhilModJunk. I got the totals to show for each subsummary.

                  Now just gotta get the calculation to do the division. The Getsummary() isn't coming out right.

                  • 6. Re: How to include the grand total in the subsummary calculation?
                    philmodjunk

                    It should, if you specify the correct break field and sort your records to match. The fraction of total summary field should also work.