8 Replies Latest reply on Aug 11, 2014 6:57 AM by soundwiz

    Calculating grand sum of sums - again

    soundwiz

      Hi.

       

      I'm calculating the stock value for products we make and the calculation is easy: StockValueProduct = ProductionCostPerItem * NumberInStock and all sorted by ProductNumber.

      I have have one table, called Transactions where I keep track of costs and revenues as well as items in and out.

      The field ProductionCostPerItem is calculated as ProductionCost / NumberOfItemsManufactured. Both these fields are summary fields so the calculation looks like GetSummary ( ProductionCost ; ProductNumber ) / GetSummary ( NumberOfItemsManufactured ; ProductNumber ) (and - yes - I check that I'm not dividing by zero).

      Also the NumberInStock is a summary field, so the stock value is calculated as GetSummary ( NumberInStock ; ProductNumber ) * ProductionCostPerItem.

      So far, so good. In my report, with a sub-summary part for ProductNumber, the stock values for each product seem to be correct.

      But no matter how I try, I cannot get the grand summary of my stock value right. I've tried using a calculation field and Sum ( StockValueProduct ) which gives me zero and I've tried using a summary field using the same field, which gives me a way to high value.

      I have no idea what's going on and any hints would be most appreciated.

       

      Per

        • 1. Re: Calculating grand sum of sums - again
          alquimby

          Per,

           

          Do you have your summary field in a Trailing (or Leading) Grand Summary part? And is ProductNumber in your sort order (since ProductNumber is your break field in your GetSummary).

           

          Al Quimby

          • 2. Re: Calculating grand sum of sums - again
            soundwiz

            Hello Al.

             

            Yes, the grand summary field is in the trailing Grand Summary part and yes, ProductNumber is my sorting order.

             

            One problem, as I see it, is that I cannot calculate the grand summary by using the same expressions as for each Product on the full record set, but I really have to sum the individual stock values.

             

            Cheers

            Per

            • 3. Re: Calculating grand sum of sums - again
              erolst

              soundwiz wrote:

              I cannot calculate the grand summary by using the same expressions as for each Product on the full record set, but I really have to sum the individual stock values.

               

              If you want to calculate a grand total with GetSummary(), use the summary field as break field, i.e. GetSummary ( theSummaryField ; theSummaryField ) – which effectively negates any existing sort order and grouping and so gives you a complete aggregated value for the found set.

              1 of 1 people found this helpful
              • 4. Re: Calculating grand sum of sums - again
                soundwiz

                This was very interesting information, but I'm not sure how this solves my problem.

                 

                The individual Stock Values are calculation fields, calculated from summaries. The cost per item is calculated from the sum of the production costs and the sum of the number of produced items. The Stock Value is calculated from the cost per item and the number of items currently in stock (which is also a sum).

                Maybe I can rearrange my calculations so that the stock values are actually summary fields. I guess I could calculate the fraction of the Stock Value that each individual item in the stock contributes and then summarize these fractions for each product. I'll have to work on this a bit.

                 

                Or is there maybe another (and simpler) way to get this information. My solution for the moment is to export the report to excel and calculate the grand sum there, but that is sort of a defeat

                 

                Thanks!

                Per

                • 5. Re: Calculating grand sum of sums - again
                  erolst

                  soundwiz wrote:

                  The individual Stock Values are calculation fields, calculated from summaries. The cost per item is calculated from the sum of the production costs and the sum of the number of produced items. The Stock Value is calculated from the cost per item and the number of items currently in stock (which is also a sum).

                  Well, I'm somewhat losing track a bit of which of your fields does what and is defined how (a description in list format serves usually better than dense prose), but if your StockValue field is just a (calculated) number field, called, say, cStockValue, for which you want to see a summarized value, then create a summary field for it: sStockValue = Total of cStockValue.

                   

                  To get the grand total for the found set, use GetSummary ( sStockValue ; sStockValue ).

                  soundwiz wrote:

                  […]

                  My solution for the moment is to export the report to excel […] but that is sort of a defeat

                  No comment!

                  1 of 1 people found this helpful
                  • 6. Re: Calculating grand sum of sums - again
                    soundwiz

                    Yes, I totally understand that. I solved it now, but I still don't know why it didn't work in the first place.

                    I have a Swedish version of FileMaker, but I'll try to explain what I do. Here's my fields:

                     

                    cProductionCost  [Calculation] = if( account = production account ; amount ; 0 )

                    sProductionCost [Summary] = Sum of cProductionCost

                    cStockBalance [Calculation] = nItemsIn – nItemsOut

                    sStockBalance [Summary] = Sum of cStockBalance

                    cManufactured [Calculation] = if( account = manufacturing account ; quantity ; 0 )

                    sManufactured [Summary] = Sum of cManufactured

                    cProdCostPerItem [Calculation] = GetSummary( sProductionCost ; tProductNo ) / GetSummary( sManufactured ; tProductNo )

                    cStockValue [Calculation] = GetSummary( sStockBalance ; tProductNo ) * cProdCostPerItem

                     

                    Now, this worked so far, but the following did not work

                    sTotalStockValue [Summary] = Sum of cStockValue (the result was zero)

                    cTotalStockValue [Calculation] = GetSummary( sTotalStockValue ; sTotalStockValue ) (also zero)

                     

                    When I did like this instead, it worked

                    cStockValueFraction [Calculation] = cProdCostPerItem * cStockBalance

                    sStockValue [Summary] = Sum of cStockValueFraction, Running count, Restart summary for each sorted group (tProductNo)

                    cTotalStockValue [Calculation] = GetSummary( sStockValue ; sStockValue )

                     

                    Maybe some day I'll understand why the first method didn't work

                    Many thanks for all help.

                    Per

                    • 7. Re: Calculating grand sum of sums - again
                      erolst

                      soundwiz wrote:

                      I solved it now, but I still don't know why it didn't work in the first place.

                       

                      Me neither; it should. See the attached file (with some background stuff made up so this works like in your setup).

                      • 8. Re: Calculating grand sum of sums - again
                        soundwiz

                        Nice!

                         

                        And weird. This is essentially what I was doing (my description was slightly simplified). I'll need to review my code and see what's wrong.

                        My feeling from earlier experiences is that it's better to make a summary of calculated fields rather than summary fields. But that was of course before I learned the trick with GetSummary you told me.

                        Thanks for that!

                         

                        Cheers

                        Per