6 Replies Latest reply on Nov 6, 2012 6:17 PM by AjEGfmTech

    Calculating Summary Fields

    AjEGfmTech

      I am looking to calculate the result of two summary fields. I tried using 'GetSummary' to get the result of the two fields but the calculated result ends up being wrong.

       

      Background

      This is a sales database. Daily sales are entered for each store. Each store has a Royalty and AdFund rate. Weekly totals are required along with the total weekly transfer (sales - (royalty+adfund)). The plays out as follows.

       

      NetSales (daily records)

      RateRoyalty (fixed)

      RateAdFund (fixed)

       

      Royalty (NetSales * RateRoyalty)

      AdFund (NetSales * RateAdFund)

      Transfer (Royalty + AdFund)

       

      TotalNetSales (total of NetSales, sorted by StoreID)

      TotalRoyalty (total of Royalty, sorted by StoreID)

      TotalRoyalty (total of Royalty, sorted by StoreID)

      TotalTransfer (total of Transfer, sorted by StoreID)

       

      GetSumRoyalty (GetSummary (TotalRoyalty; StoreID)

      GetSumAdFund (GetSummary (TotalAdFund; StoreID)

      TotalTransferCalc (GetSumRoyalty + GetSumAdFund)

       

      The problem has to do with 'TotalTransfer'. This amount frequently does NOT equal TotalRoyalty + TotalAdFund. This is probably because TotalTransfer is the sum total of each of the seven daily 'Transfer' values in the week and therefore is subject to a slight differences compared to when the sum totals are added to each other. Following is sample data.

       

      RateRoyalty = 0.06

      RateAdFund = 0.02

       

      Day, NetSales, Royalty, AdFund, Transfer

      Mon, 1565.36, 93.92, 31.31, 125.23

      Tue, 1850.79, 111.05, 37.02, 148.06

      Wed, 1703.38, 102.20, 34.07, 136.27

      Thu, 2808.33, 168.50, 56.17, 224.67

      Fri, 2173.52, 130.41, 43.47, 173.88

      Sat, 2127.16, 127.63, 42.54, 170.17

      Sun, 1508.27, 90.50, 30.17, 120.66

      Totals, 13736.81, 824.21, 274.74, 1098.94

       

      The last number should be 1098.95 if you take 824.21 + 274.74. That is the crux of the problem.

      The thing is, that 'TotalTransferCalc' produces the same 1098.94 result?!? I would have expected (wanted) it to produce 1098.95. Why doesn't it?

       

      Any help appreciated. Thanks.

        • 1. Re: Calculating Summary Fields
          comment

          Isn't this the same question as before?

          https://fmdev.filemaker.com/thread/67871?tstart=0

          • 2. Re: Calculating Summary Fields
            AjEGfmTech

            Nope. It is the same database solution that I'm working on, but the other post was about problems I was having with getting the rounding error corrected, this one is about why the GetSummary function does not seem to work to add to summary fields together. While these posts have to deal with resulting amounts being off by pennies, they are two different parts of the sought after solution.

             

            Thanks for asking though.

            • 3. Re: Calculating Summary Fields
              comment

              ajegfmtech wrote:

               

              this one is about why the GetSummary function does not seem to work to add to summary fields together.

               

              The GetSummary() function is not supposed to add summary fields together. But that's not important, because AFAICS you are using it correctly.

               

              I am still not sure what your "new" question is. The penny discrepancy is clearly the result of rounding error and nothing else. You say that Royalty = NetSales * RateRoyalty, but your data shows that it's actually rounded to two decimal places, as is AdFund. Remove the rounding and it will all match at 1098.9448.

               

              Message was edited by: Michael Horak

              • 4. Re: Calculating Summary Fields
                keywords

                If you add up your AdFund totals you get 274.75. Back to your rounding issue! Try rounding Royalty, AdFubd and Transfer and see what you get.

                • 5. Re: Calculating Summary Fields
                  AjEGfmTech

                  Thanks for the reply Michael and for taking the time to look at my post thoroughly. You are correct, the data I posted does show only two decimals. That's because I have the data formatting set to two decimals. The actual data is the 1,098.9448. I had removed all rounding during my posts about the 'Round function'. My new question is actually not about the rounding, even though that discrepancy is what I'm trying to work around.

                   

                  All of the data I submitted is actually out to four decimal places, its just the formatting that only shows the two decimals I posted here.

                   

                  I was not trying to have the 'GetSummary()' function add summary fields. I was trying to add the RESULT of a GetSummary() function, but it didn't work. My 'new' question is simply, shouldn't adding the result of two GetSummary() functions equal the sum of those two Summary totals?

                   

                  ...

                   

                  Thanks Michael. Your questions got me to look at the elements and results again. I found my problem. I was presuming that my adding the GetSummary() results was not working properly because the 'TotalTransferCalc' result was identical to the 'TotalTransfer' result. I had displayed both GetSummary() results separately to make sure that they were producing the desired result. By initial inspecition, they were working correctly because they showed 824.21 and 274.74. However, you question reminded me that I was seeing the result with the display data formatting set to 2 decimals. Underneath, the real resulting values of the GetSummary() functions were 824.2086 and 274.7362. When I added those together, it was no wonder that the result was still 1098.9448, which rounds to 1098.94.

                   

                  The solution then was not to remove the rounding, but rather introduce it, but at the right point.

                  I changed the GetSummary() field calculations to below and it is now producing the correct result.

                  Round ( GetSummary ( TotalRoyalty ; StoreID ) ; 2 )

                  Round ( GetSummary ( TotalAdFund ; StoreID ) ; 2 )

                   

                  So, after all of this, the GetSummary() is working to allow calculating summary fields. Thanks for your help Michael.

                  • 6. Re: Calculating Summary Fields
                    AjEGfmTech

                    Thanks for looking into this and replying 'keywords'. I was replying while your post must have come in. I found the answer, I needed to round the GetSummary() results. Thanks for your suggestion too though.