
1. Re: Calculating grand sum of sums  again
alquimby Aug 10, 2014 12:14 PM (in response to soundwiz)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 Aug 10, 2014 12:37 PM (in response to alquimby)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 Aug 10, 2014 2:32 PM (in response to soundwiz)1 of 1 people found this helpfulsoundwiz 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.

4. Re: Calculating grand sum of sums  again
soundwiz Aug 11, 2014 3:37 AM (in response to erolst)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 Aug 11, 2014 3:59 AM (in response to soundwiz)1 of 1 people found this helpfulsoundwiz 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!

6. Re: Calculating grand sum of sums  again
soundwiz Aug 11, 2014 5:12 AM (in response to erolst)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 Aug 11, 2014 6:06 AM (in response to soundwiz)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).

SummaryTest_eos.fmp12.zip 12.6 K


8. Re: Calculating grand sum of sums  again
soundwiz Aug 11, 2014 6:57 AM (in response to erolst)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