13 Replies Latest reply on Nov 20, 2016 12:21 PM by SvenLoetscher

# Weighted Average Percentage in Grand Total

I try to show the grand total percentage of net income based on two different flats but can't get my head around it.

I can summarise the total net gain but can't produce a summary field for the total net percentage gain.

I've attached my sample file for your review.

Thanks!

• ###### 1. Re: Weighted Average Percentage in Grand Total

Judging from your screenshot, you need a calc field like

GetSummary ( totalNet ; flat ) / GetSummary ( totalNet ; totalNet ) * 100

where flat is the break field of your sub-summary part

• ###### 2. Re: Weighted Average Percentage in Grand Total

I've tried that but GetSummary is a calculation and won't display in the grand total sum

• ###### 3. Re: Weighted Average Percentage in Grand Total

I realise I didn't understand your goal. Maybe explain it again - and guess a screenshot with markers might help., and the value you are expecting to see.

Speaking of screenshots: you can embed images inline, rather than as attachments. That makes it usually easier to see them and read the text simultaneously.

• ###### 4. Re: Weighted Average Percentage in Grand Total

Hi,

I'm guessing that you need to "atomize" your percentage value.  For example, each line item (the stuff in the Body part) would have a calculation like Dollars / GetSummary(SumDollars; SumDollars).  Then, you'd create a summary field that is the total of these "atoms".  This summary should evaluate properly in both sub and grand summaries.

• ###### 5. Re: Weighted Average Percentage in Grand Total

a bit more ...

based on the model above, the grand summary of the atomized percentages should be 1 (or 100%).

• ###### 6. Re: Weighted Average Percentage in Grand Total

Here's the embedded image of my attached sample FileMaker database.

David, the grand summary answer in my example is the percentage gain/loss from all purchases, ,cost, sale and income divided by purchase. In this example that would be  (-300'000 -20'000+255'000+40'000)/-300'000*-1=-25'000/-300'000*-1=-8.3%

Also, the sub summary part is shown when sorted by flat (i.e. A1 or B2)

I think the answer lies in creating a field in the body that is a Getsummary of total Accounting divided by "something".

Thanks for any help.

• ###### 7. Re: Weighted Average Percentage in Grand Total

How did get at the individual summarised figures in the "bottom line"?

Do you have fields like

cSale = Case ( type = "Sale" ; amount )

sSale = Total of: cSale

(Repeat for the three other types)

• ###### 8. Re: Weighted Average Percentage in Grand Total

I do this by creating a calculation field that does your summaries and I usually do this with SQL.  To do this, you need to know the found set of records and you can do this with a Summary Field that Lists all of the primary keys of the found set.  From there you use the SQL to get the summary.  It might be something like this:

Create Field "__pKey_s" that is Summary if the primary key field, say "__pKey" that lists those values.

Then do a calculation summarizing the found set:

Let ( [

F1 = TableName::__pKey_s ;

F2 = Substitute ( F1 ; ¶ ; ", " ) ;

F3 = "SELECT

SUM ( Purchase )

FROM

TableName

WHERE

\"__pKey\" IN ( " & F2 & " )" ;

F4 = If ( Not IsEmpty ( F1 ) ; ExecuteSQL ( F3 ; ¶ ; ¶ ) ) ;

F5 = GetAsNumber ( F4 )

] ; F5 )

Note that F2 assumes the primary key field is a number field.  If it is a text field (say UUID), then you have to have single quotes around each result such as:

F2 = "'" & Substitute ( F1 ; ¶ ; "', '" ) & "'" ;

After you have created a calculation field that does each of these summaries, then you create another calculation to do the equation you needed of the summary fields.

• ###### 9. Re: Weighted Average Percentage in Grand Total

FYI, here is the database re-written with the summaries as calculation fields.

• ###### 10. Re: Weighted Average Percentage in Grand Total

Here is a screen capture with the new calcs in the brown area at the bottom

• ###### 11. Re: Weighted Average Percentage in Grand Total

Awesome, thank you so much!

I come to the 'sad' conclusion that I'll have to teach myself SQL after all!

...or I'll contract you!

• ###### 12. Re: Weighted Average Percentage in Grand Total

SQL is a great tool.  It can be very complicated, but it doesn't have to start that way.  Just learning some basic SELECT statements will get you a long ways.

TMS would be happy to contract with you.  We are located in Texas.  But the odds are that you are not in Texas too.  Finding a consultant that is close to you and able to meet with you is often a better business relationship.  You can always go to FileMaker Consultants, Data Consultants, Database Consultants    and search for a developer near to you.  But if you don't find one you like, we would be glad to help out with your development work remotely.

• ###### 13. Re: Weighted Average Percentage in Grand Total

Thanks for the hint. I guess it'll be much easier to work with someone in driving distance.

I've updated my 'real' file with your code and everything works great, with the exception that the SumNetPct_c field now has to average 3'000 records, which makes it almost impossible to use (I've aborted after a 5' wait).

Is there any trick that you could think of to speed up this process? The field needs to be recalculated for every group I search for or for all records (and the records will double in the next weeks).