I have a summary field type called "group_total" which is the total of all the responses for each item in each event.

I need to find out the percentage of the each of the item from the group total of the current event.

I defined a field called item_percentage be a calculation of (item_count / group_total) * 100

i.e.

event 1

item_type item_count

item A - 2

item B - 3

item C - 9

item D - 0

group_total -14

event 2

item_type item_count

item A - 3

item B - 0

item C - 7

item D - 4

group_total -14

For some reason I am not getting the correct percentage. In fact when my data is

event 1

item A - 1

item B - 0

item C -0

item D -0

group_total = 1

I get a percentage of 6.25 for item A which should be 100% I can't seem to figure out what is wrong with my field/calculation.

Can anyone help?

Thanks.

Hi,

group_total ?

Is it a Sum(SelfJoinOnItem_Id) or classic statistical field ?

group_total is actually a field that I ended up creating when I was making a new layout/report with grouped data that includes subtotals and grandtotals. I believe it is in the Specify Subtotals dialog box and I clicked on "Create Summary Field" and I clicked on "total of" and specifed "item_type" as the field to create a subtotal of.

I don' think it is a sum(selfjoinonitem_id), so I guess it is a statistical field...

Did I do something foolish?

Your summary field won't be useful here as it takes into account all "event", while you want a count per event.

A count per event would be made by a SelfJoin on Event with relationship Event_Id::Event_Id

Then the count would be Sum(SelfjoinOnEvent::Item)

Your percentage calculation should now be acurate...

Whoa! We're going down the wrong path here. You don't need self joins to accomplish what you wish. The entire problem is that you can't define calculation fields using Summary fields without using the GetSummary(summary field, break field) to extract the summary data.

If your calculation field is to be placed in a subsummary by event, it needs to look like:

PercentEvent (calculation, number) =

(GetSummary(Count, Event)/ GetSummary(Total, Event)) * 100

If you need percentages for other subsummary or summary types, they need to be defined specifically for that type. For example, in a Trailing Grand Summary:

PercentGrand (calculation, number) =

(GetSummary(Count, Count)/ GetSummary(Total, Total)) * 100

This is what is causing your result to be incorrectly calculated.

-bd

Glad you fixed this one. I was coming back to edit the post.

Ugo and LiveOak, I tried both of your methods and both equally work. I definitely have a lot to learn when it comes to calculations/scripts and fields.

I realize that a majority of this tricks and methods is learned by experince and trial and error but I was just wondering if there is any book out there that you can recommend that provides a bit more detail and insight into calculations/scripts and fields?

I have the FileMaker Pro Bible but only seems to scratch the surface of FileMaker's power.

Thanks again for everyone's wisdom

Hi Macaroni,

Advanced FileMaker Pro 5.5 Techniques for Developpers by Chris Moyer and Bob Bowers is a good one.