Averaging Subsummaries

[sgonzo]

I have fields:

Supergroup, Group, Subgroup, otherfields

I need to extract one max Subgroup number for each Group, sum these highest Subgroup numbers, and divide this sum by the max number for Group.

In other words, I need the average of the highest Subgroup number in each Group across the whole Supergroup.

I can get the Subgroup high number in a subsummary for Group, but how do I get the average all these subsummaries maximums for each Supergroup?

Thanks for any assistance.

[Slobey]

If I understand you right I think the best way is to create a self relationship for each field and create a sub summary calc field based on that relationship. Then create a field that averages those.

[sgonzo]

Here's some data:

supergroup1 group1 subgroup1

supergroup1 group1 subgroup2

supergroup1 group1 subgroup3

supergroup1 group2 subgroup1

supergroup1 group2 subgroup2

supergroup1 group2 subgroup3

supergroup1 group2 subgroup3

supergroup1 group2 subgroup4

supergroup1 group3 subgroup1

supergroup1 group3 subgroup2

supergroup1 group3 subgroup2

supergroup1 group3 subgroup2

so i want to know that max subgroup within group1 is 3, within group2 is 4, within group 3 is 2.

So 3 + 4 + 2 = 9 divided by 3 groups = average highest subgroup is 3 for all of supergroup1

i can make a self join table with these fields equal to themselves, but I don't understand the sub summary calc field based on that. Do I make a summary field, or a calc field? The logic of Filemaker still eludes me.

[Slobey]

You would make a calc field:

Sum (selfjoin relationship::field) This will give you a summary without needing a found set or subsummary layout. Then you make a field to average those.

[sgonzo]

I need this spelled out in a little more detail.

I set up a self join table, so supergroup = supergroup, group = group, and subgroup = subgroup. (or is it some other relation comparator?) How do i get the maximum subgroup number from this? Do I need to set another field to be Max_Number_of_Subgroup and self-join that to subgroup? If so, how do I restrict this so that I don't get the maximum subgroup across all groups?

Pretend I'm real stupid, and give me all the details (actually, you might not have to pretend that I'm stupid).

Thanks.

[Slobey]

I think I may have misunderstood. How are groups 1, 2, and 3 gathered? Is there a common field?

[sgonzo]

This is a vastly simplified version of some data collected by a scientific experiment. The experiment groups things, and I need to give them some averages of high groupings. So the data looks like this:

record 1: 1 1 1

record 2: 1 1 2

record 3: 1 1 3

record4: 1 2 1

record5: 1 2 2

record6: 1 2 3

record7: 1 2 3

record8; 1 2 4

record9; 1 3 1

record10: 1 3 2

record11: 1 3 2

record11: 1 3 2

record12: 1 3 2

(record13:) 2 1 1

etc.

so for every change in the middle number field (group) I want to know the highest number the right number field (subgroup) got to.

When I've gotten all that, I want to know the average of all those highest numbers for all of the numberfield on the left (supergroup).

[comment]

IMHO, these things are MUCH easier when using a relational database.

dataSummarize.fp7.zip

[sgonzo]

Not to offend the forum, but can you suggest a database to use? I'm experimenting with the trial version of filemaker to see if it can do what I need. I was churning a whole lot of data using excel macros and applescripts, and thought that it's time for it to all go in a database.

I must say that even though I've read the user manual and a (not very good) book on Filemaker, the logic of the program still hasn't clicked for me. Of all the programs I've ever learned, this has taken the most time without a feeling of understanding.

It seems that a lot of the design is pointing fields back at themselves and then filtering, but then I really don't get it so my judgement is biased.

[comment]

Excuse me, the subtle humor obviously didn't work. I was referring, of course to Filemaker. Filemaker is a relational database, and if you will use it as such, it will give you exactly what you've asked for.

I have tried to demonstrate this in the attachment to my previous post, but apparently that too has gone unnoticed.

[sgonzo]

I don't really understand why you're insulting me. Your answers may seem clear to you, but perhaps you could have empathy for someone new to the program.

I'm just trying to figure out how to do something slightly complex, and I admit I'm not well versed in the program. If you applied the energy spent on insults and sarcasm, and created a clearer explanation, maybe I wouldn't aggravate you so much that you feel the need to belittle.

I don't see the attachment on your earlier post, but then that's the definition of a student of something, they don't know everything yet.

[comment]

I have no intention of insulting you, and if that's how it came accross I can only apologize - again.

To download the attachment, look in the header of my message, under the bold "Re: Averaging Subsummaries".