Sum of values within a range in a field?

[niktemadur]

Hello everybody!

I have a database with a hundred records (so far).

I have a field that calculates everyone's age in years only.

I would like to create a statistical layout with several similar fields:

#1 would display the total of people between the ages of 15 and 20.

#2 would display the total between 21 and 25.

#3 would display between 26 and 30.

And so on.

Seems pretty straightforward, but I just can't figure this one out with either a summary or calc field in one single, simple step. I guess I could do it by creating several intermediate processes, but this seems both inelegant and unnecessary.

Can anybody help? Thank you. I love this forum. I'm learning tons just by reading other threads, but every once in a while I'm stumped. Like today.

And just what does the Graemlin with his tongue hanging out stand for?

[ernst]

Hi,

I think you'll need to define two extra fields:

-one calculated field for the age_group, which should have a definition like

Case((age 15) and (age < 20), "1", (age 20) and (age< 25), "2" , etc.

-one summary field with definition: count of age

Then you should make a new layout with a summary part that displays the age group and summary fields.

If you sort the found records by age_group and view the layout in preview mode you'll see your report.

Hope this is sort-of-halfway-understandable....

Regards,

Ernst.

[niktemadur]

Interesting, Ernst, thanks! I'll try it out.

Here's the "unelegant" way I found:

Calc Field 1: =If(Age >= 15 and Age <= 20, 1, 0)

And again

Calc Field 2: =If(Age >= 21 and Age <= 25, 1, 0)

And again

Calc Field 3: =If(Age >= 31 and Age <= 35, 1, 0)

All the way to seventy in ranges of five years per calculation.

Then, this:

Summary Age Total 1: = Total of Calc Field 1

And again

Summary Age Total 2: = Total of Calc Field 2

And again

Summary Age Total 3: = Total of Calc Field 3

etcetera

And by Percentaje

Summary Age Average 1: =Average of Calc Field 1

And again

Summary Age Average 2: =Average of Calc Field 2

And again

Summary Age Average 3: =Average of Calc Field 3

etcetera

Every day, in every way, I am getting better and better at Filemaker.

Every day, in every way, I am getting better and better at Filemaker.

Every day, in every way, I am getting better and better at Filemaker.

[Riley Waugh]

I'm confused... how do you get an average age... wouldn't average of calc field 4 be <=1 since all values in that field are either 1 or 0?

Could you not change your Calc Fields to be...

Calc Field 1 = If(Age>=15 and Age<=20,Age,"")

Calc Field 2 = If(Age>=21 and Age<=25,Age,"")

Calc Field 3 = If(Age>=26 and Age<=30,Age,"")

if you use "0" instead of "" then the summary fields using "Count of" will count the "0"s also which is not what you want.

now, make two more sets of fields.....

Average 1 {summary field} = Average of Calc Field 1

Average 2 {summary field} = Average of Calc Field 2

Average 3 {summary field} = Average of Calc Field 3

etc.

Count 1 {summary field} = count of Calc Field 1

Count 2 {summary field} = count of Calc Field 2

Count 3 {summary field} = count of Calc Field 3

etc...

This will give you (in the body of the report) the count by each five year breakdown and hte average of each 5 year breakdown. You still can use sub-summaries etc to give other information