Jump to content
Sign in to follow this  
macaroni

gettin the percentage from the summary group total

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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? frown.gif

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 smile.gif

Share this post


Link to post
Share on other sites

Hi Macaroni,

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.