7 Replies Latest reply on Oct 24, 2014 2:57 PM by cowens1

    Sub-Summary Percentage

    cowens1

      Hello All,

      I have a report showing a list of sales calls, sorted by salespeople.

      There is a summary set for each person that tallies the number of calls they ran, the number of sold jobs and the total Sales in Dollars.

      In the Header I have a calculation that divides the Summary TotalCallsSold by the Summary TotalCallsRan, resulting in an overall closing ratio.

       

      QUESTION:

      How can I get a ratio per salesperson to show up on the Sub-Summary line?

        • 1. Re: Sub-Summary Percentage
          mikebeargie

          If your summary/calc fields are based on the same table as your layout is, then you should just be able to copy those fields into the subsummary layout part, and they will summarize by each subsummary instance. Filemaker treats every subsummary group as an independent set of records (with some exceptions of course).

           

          You might also consider a trailing subsummary for these fields.

          • 2. Re: Sub-Summary Percentage
            cowens1

            The problem I'm facing may be a little more complicated than that.

            While all fields are based on the same table, I can't calculate a ratio of a found set or subset using only one field.

             

            I use a summary field to count all records in the found set. It also counts all the records in each Sub-set. 

            Another Summary field counts the records marked "Sold" in the found set and each sub-set.

             

            When I put a calc field (that divides the sold summary by the total summary) on the header and each Sub-Summary line, it returns the results of that calculation for the Found Set, and returns the exact same numbers on each sub-summary line. It does not recalculate in consideration of the various sub-summary numbers.

             

            Any suggestions?

             

            Collins

             

             

            Sent from my iPad

            • 3. Re: Sub-Summary Percentage
              debi

              Collins,

               

              Apologies if I don't understand your predicament precisely enought to provide an exact calc, but I think you need to use the GetSummary function. Something along these lines:

               

              PersonRatio =
              GetSummary ( TotalCallsSold ; Person ) / TotalCallsSold

               

              Does that help?

               

              Debi Rubel
              FullCity Consulting

              • 4. Re: Sub-Summary Percentage
                cowens1

                Thank you for pointing me in the right direction, Debi!

                 

                My actual calculation was:

                 

                GetSummary ( Summary_CountSold ; _kf_TechNumber )  /  GetSummary ( Summary_CountCalls ; _kf_TechNumber )

                 

                This resulted in a closing ratio for each person in the found set, which was sorted by salesperson so the result was in the SubSummary Header.

                 

                This forum is awesome! 

                I didn't know / wasn't aware of how to use the "GetSummary" function.

                 

                Thanks again,

                 

                Collins

                • 5. Re: Sub-Summary Percentage
                  debi

                  Glad that helped - thanks for letting us know!

                  ~debi

                  • 6. Re: Sub-Summary Percentage
                    dgroyal

                    Excellent, this is just what I was looking for.  Thanks!

                    • 7. Re: Sub-Summary Percentage
                      cowens1

                      Glad it helped!