Jump to content

Averaging Subsummaries

This topic is 6625 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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


Link to comment
Share on other sites

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


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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6625 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

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