13 Replies Latest reply on Jun 23, 2014 9:18 AM by d24601

    Calculating Ratios of Subtotals in Sub-Summary Parts

    richardsrussell

      Please indulge me as I work my way up to my problem by reviewing the various things I've tried that didn't work.

       

      To illustrate the problem, I've ginned up a little toy database that boils it down to its essence by using a super-simple demo example of a school PTA trying to raise money by doing candy sales. We know how many kids there are in each classroom and how much money each class raised, and we want to do a simple analysis involving 2 calculated ratios:

      • this year's sales per kid (a dollar result)

      • this year's sales compared to last year's for the same classroom (a percent result)

       

      Here's what we start out with ("Preliminary" layout):

       

      Preliminary.png

       

      (Note the particularly sterling performance by Ms. Clark's 3rd graders, perhaps due to little Billy Gates transferring in this year.)

       

      The "Totals" are just that, summary-field totals of the detailed numbers in the column above. Obviously the 2 pink values are meaningless, since the total of an average is virtually never the same as the average of the totals, which is what we're really interested in. (I didn't actually try this, I just threw it in the demo file for completeness.)

       

      So what will work? Next I tried using a different kind of summary field, the average. You see the results in blue — the average of each column per record (for example, $1,004,800 in sales divided by 5 classes = $200,960 per class). But we didn't want "per class", we wanted "per kid". Onward!

       

      Next I tried to see if a weighted average would work. I calculated the summary-field average of "Sales2013" weighted by "Kids" for Column E and by "Sales2012" for Column G. But no, this took the numbers in the wrong direction, as shown in green. Weighting evidently applies to the numerator, not the denominator.

       

      So that led to trying something other than summary fields, namely a calculation that used summary fields as its components. The formulas were:

      • Ratio per Kid = ∑ Sales2013 / ∑ Kids

      • Ratio per PrevYr = ∑ Sales2013 / ∑ Sales2012

      And this seemed to produce the desired results, as indicated by the purple fields above. $1,004,800 in sales divided by 128 kids really is $7,850 per kid, and divided by $7,000 the prior year really is a 14,354% increase.

       

      So I duplicated the layout, called it the "Final" layout, and modified it in order to concentrate on just the parts that seemed to work:

       

      Final.png

       

      (Aside: Notice that the purple background came over just fine for the ratios.) Not done yet, tho, because what we're looking at here is all 5 records in the file. Would those calculations still work for a subset of them? For example, for all the female teachers?

       

      Females Only.png

       

      Or all male teachers?

       

      Males Only.png

       

      And the answers were "yes" and "yes". So this looked like success (aside from the purple background to the ratio fields having mysteriously vanished).

       

      But now we come to the gory bit, and this is where I get to point out my problem before throwing myself on the mercy of the assembled masses. Would that same trick work within a sub-summary field? That is, if I sorted all 5 records by sex, could I get subtotals showing $1,002,700 for women teachers and $2,100 for men teachers, on the same report at the same time? Let's look:

       

      Subtotals.png

       

      Answer: NOOOO! Each of the numbers that's supposed to be a ratio of subtotals is actually just the ratio of the grand totals.

       

      Thus my problem, complaint, and query: How can I show an accurate ratio of 2 subtotals within a sub-summary part?

       

      I am attaching my toy file in case you want to play around with it. I ginned it up using FMPA 12.0v4 running under Mac OS X 10.8.4.

       

      Also, quite as an aside, and in no way critical to my main question, if you have any idea where the purple background on my ratios went, that's irritating me, too. I assure you it's still there in Layout mode.

        • 1. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
          taylorsharpe

          I would just use SQL totals for summary results.  This will work in sub-summary parts, but not the grand total.  But you already have the fields calculated for the grand total.  Here it is. 

           

          PS:  Note that by doing a SQL calcluation, this ONLY works if you have sorted by Sex.  Basically, it is doing a calcluation based on the last record before the summary.  This calculation is at the record level, but the sub-summary recognizes it.  If you want other sub-summary calcluations, you'll have to alter the SQL based on the sub-summary group's field name. 

          • 2. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
            alquimby

            Richard,

             

            I am not 100% sure what you are trying to do here, but the attached shows a "work around" that uses calculations (I call them "temporary") to grab summary results from a found set. You can then do additional calculations on the temporary fields. Not using summary fields, but the calculations may get the answers you are looking for.

             

            And no, I don't know why the pink backgrounds don't appear in browse mode. I set a pink background for my fields also.

             

            Al Quimby

            • 3. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
              richardsrussell

              "I am not 100% sure what you are trying to do here"

               

              What I am trying to do is calculate ratios between 2 subtotals within a sub-summary part that will come out mathematically accurate. (For example, in the example above, there's one subsummary of 2 records, among the 5 records total, which shows 54 kids having raised $2,100, and I want to divide $2,100 by 54 to show an average of $39 raised per kid.)

               

              The way I was hoping to do it was with native FileMaker Pro summary or calculation fields. Calculation fields work just fine for grand totals (including grand totals of found subsets) but not for sub-summaries within a larger set.

               

              My client is sufficiently interested in getting this kind of analysis for his business (which involves considerably more records and larger dollar amounts than my candy-sale example) that I'll use a workaround or SQL if I absolutely must, but I'd really rather just use regular FMP commands if there are any that'll work. However, it's starting to look as if this is a lacuna in their toolset.

              • 4. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
                alquimby

                Richard,

                 

                Usually when I'm butting my head against a wall it's because I'm looking at the records in the wrong way––so I have to de-construct then re-construct. In the attached there is a separate record for each year/teacher combination. I don't put 2 years in the same line item. It simplifies calcs and summaries. There are 3 scripts.

                 

                Al Quimby

                1 of 1 people found this helpful
                • 5. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
                  debi

                  Richard,

                   

                  I haven't checked out all the attachments or reviewed your request thoroughly enough to provide a defnitive answer, but I think you may be well served by investigating the GetSummary ( summaryFiled; breakField ) function. http://www.filemaker.com/12help/html/func_ref3.33.47.html

                   

                  Does that help?

                   

                  Debi Rubel,
                  FullCity Consulting

                  • 6. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
                    alquimby

                    Richard,

                     

                    Even before Debi's post, I tried various GetSummary calcs but found none that worked.

                     

                    Concerning part of your last post: "For example, in the example above, there's one subsummary of 2 records, among the 5 records total, which shows 54 kids having raised $2,100, and I want to divide $2,100 by 54 to show an average of $39 raised per kid.)":

                     

                    I would argue that your subsummary shows only that 2 records with the attribute Males raised $2100. At the record level, there are 2 sexes, which can be summarized, and 5 class sizes (kids––20, 30, 24, 25, 29), which can be summarized. I summarized the class sizes in the attached (use the Sort by Class Size script.) But since there is no class size of 54, you can't summarize by 54. Even though class size is a number that can be totalled, it still is just an attribute of each record, thus only each distinct class size can be summarized. The way your file is set up, I see no way of summarizing any fraction or total of the 5 sizes. You are going to have to use "work arounds" (in my humble opinion–but I'm often wrong on FileMaker matters).

                     

                    Al Quimby


                    • 7. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
                      LyndsayHowarth

                      Debi is right... It is a matter of having a hierarchy of GetSummary Calcs for each sort level and part that you may need to use. Sometimes it is a matter of calculating GetSummary / GetSummary.

                       

                      - Lyndsay

                      • 8. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
                        alquimby

                        I tried several GS/GS and sorted accordingly, but with no success. Hopefully someone with more skills than I have will take the challenge and come up with what Richard is seeking (getting that ratio on that layout in that sub summary part without a "work around").

                        • 9. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
                          robwoof

                          Hi Richard,

                          After watching this trail develop over the last few days, I started to wonder why no-one had answered it satisfactorily yet. I had a bit of a play with it, and came up with the following two changes:

                          1. Avg byKid: now "Average of Avg2013, weighted by Kids". The theory here is that each record has "average sales per kid" (Avg2013), and you want to know the overall average of whichever summary group. So you average the average, weighting the average by the number of kids in each record's average.
                          2. Chg Totals: now a Summary field, "Average of Chg2013, weighted by Sales2012". The theory here is (again) that you want to average a quotient, so you take the average weighted by the divisor.

                          Make those changes, then have another look at the Final layout (you will need to add the Avg byKid field to the subsummary and Grand Summary parts on the Final layout as well). Note also that the purple fill only seems to appear when the record immediately above the respective sub-summary part is the active record. Why the fill disappears at other times is beyond me.

                          I hope this makes sense.

                          Cheers,
                          Rob

                          PS I have added an updated version of the file for your reference.

                          • 10. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
                            taylorsharpe

                            For all this other discussion below, the example I gave above with ExecuteSQL works just as you requested.  It requires sorting, but you need sorting for subsummary results anyways. 

                            • 11. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
                              alquimby

                              Wow! Great job. It was my lack of math skills that kept me from getting there. I would never have figured out the "average of sales 2013 weighted by sales of 2012."

                               

                              Al Quimby

                              • 12. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
                                richardsrussell

                                Rob, your solution worked like a charm. It's kind of a kludge for a capability that, IMHO, FileMaker should have built into the base product, but it works just fine, and it does so using native FMP commands.

                                 

                                My client was so delighted with the result that he'd like to send you a small token of his appreciation. If you e-mail your postal address to me at RichardSRussell@tds.net, I'll see that he gets it.

                                 

                                Thank you!

                                • 13. Re: Calculating Ratios of Subtotals in Sub-Summary Parts
                                  d24601

                                  I realize I'm almost a year late to this party, but the GetSummary solution worked like a charm for me.

                                   

                                  Thanks Debi!