8 Replies Latest reply on Dec 24, 2010 11:40 AM by philmodjunk

    Report: grouping percentages

    ireneprins

      Title

      Report: grouping percentages

      Post

      Hi,

      I have created a report in FILEMAKER 11, in WINDOWS XP. The data base belongs to a nonprofit association. There are incomes and spendings in different villages. The fields represent concepts (I have substituted them for names of fruits) and the percentages that these represent with regard to the total amount.

      So now I have created a report that is supposed to GROUP the concepts, first by village, and secondly grouping by province (so a secondary level, grouping the grouped data). The amounts are correctly summed but not the percentages.

      If anyone could give me a hand with this I would be MOST GRATEFUL!!! I really don't know what I can be doing wrong!!

      Villages_and_province.png

        • 1. Re: Report: grouping percentages
          philmodjunk

          So for "Apple" in the Algorta villate, you need to compute 6/13 = 46% and for Artaza, this would be 100% for Apples?

          If so, define a calculation field:

          Amount / GetSummary ( Amount ; VillageField )

          This assumes a single record for Apple in a given village and that your records are sorted by VillageField.

          Edit Note: the above expression is incorrect. It should read:

          Amount / GetSummary ( sTotal ; VillageField )

          Where Amount is the data field and sTotal is a summary field.

          • 2. Re: Report: grouping percentages
            ireneprins

            Thank you very much for your reply. I am very sorry not to have answered before, as I have been away for a few days.

            I understand your answer and it works on the Village level (as you mention, it only works for single concept records in a given village. As you can see in the exemple, it doesn't work in the case of  PEARS in PORTU because, we understand now, there are more than one records for PEARS in PORT.

            So the next question is: how can we get to the PROVINCE level? The formula does not work there, because there are always more records for each fruit (at least one for each village).

            Thank you again for your kind help!!!

            Best regards

            Irene

            • 3. Re: Report: grouping percentages
              philmodjunk

              Instead of Data field divided by the results of the get summary function, use two get summary functions with different break fields.

              GetSummary ( sTotal ; VillageField ) / GetSummary ( sTotal ; ProvinceField )

              Should compute the village percentage of the total for the province. To get other fractions, just use different break fields.

              Note:  I see I made a mistake in my original field using "amount" as though it were both a summary field and a normal data field--which isn't possible of course!

              The key detail here is to specify break fields that correctly identify the groups into which your are sorting your records and then to also make sure your records are correctly sorted by these "break" fields.

              • 4. Re: Report: grouping percentages
                ireneprins

                Thank you!

                I have just created the formula following your instructions:

                GetSummary ( total cantidad; localidad) / GetSummary ( total cantidad; Provincia)

                (en English: 

                GetSummary ( s Total; village field) / GetSummary (S Total; Province field)

                But to my big surprise the results are ZERO for all the concepts (in the report as well as in the form). I don't understand how this can be possible!

                Have you got any idea???

                Thank you again!

                 

                • 5. Re: Report: grouping percentages
                  philmodjunk

                  Is s Total a summary field?

                  Are your records sroted first by Province Field, Then by Village Field?

                  • 6. Re: Report: grouping percentages
                    ireneprins

                    Yes, s Total is a summary field.

                    In my report, the records are sorted first by Province Field, then by Village Field.

                    And I have established the order by three priorities: Province, Village and Concept.

                    By the way, the formula you gave us, computes the percentage of the total amount (all concepts together) of each village for the total of the province. But it doesn't compute the percentage of each concept.

                     

                    Would it be necessary to calculate a Getsummary between concept and village? Like this:

                    GetSummary ( s Total; concept field) / GetSummary (S Total; Province field) (for the province report)

                    and:

                    GetSummary ( s Total; concept field) / GetSummary (S Total; Village field) (for the villages report)

                     

                    • 7. Re: Report: grouping percentages
                      ireneprins

                      I've got it RIGHT now!!

                      I did that last thing I suggested to you, using those two formulas, relating concept and province, and then concept and village.

                      And now my report is correct.

                      Thanks so much for helping me to reason, it's so hard sometimes to understand the logic behind the tools.

                      MERRY CHRISTMAS!!!!

                      • 8. Re: Report: grouping percentages
                        philmodjunk

                        You seem to be understanding the concepts and I don't see any mistakes in how you've set this up. That doesn't explain why you're getting a result of zero, however.

                        Compare what you've set up to this demo file. (There are two layouts, one with a table view and one set up as a summary report.)  http://www.4shared.com/file/UQMoWGBf/PctOfSubTotals.html