Summary Field Averages - Rounded

[phuebs]

Not sure if this is the right place to post this but.....

I have a "yearly summary report" in which I would like to have an average of averages.

Each month I have a rounded stat. I created in my yearly report a summar field which is a year average of those monthly averages (if that makes sense).

Here's the problem...how do I round that number? I don't see any way to create a rounding equation for a summary field.

Thanks for your help

[CobaltSky]

If you require rounding just for the purposes of display, try setting the number format (for the summary field on the layout) to a specific number of decimal places.

If you require the rounded number for purposes other than display, if it's an overall summary, set up a calculation field defined as:

Round( YourSuumaryField; n)

(where "n" is the number of decimal places you wish to round to).

If you require the calc to run correctly through sub-summary groups, use:

Round( GetSummary( YourSummaryField; BreakField); n)

Where "BreakField" is the sort field you will be using to group the data for the sub-summaries. :wink2:

[phuebs]

Worked!

Thanks a lot

[comment]

I just hope you're aware that an average of monthly averages is NOT the yearly average.

[phuebs]

Makes sense...I need help then!

Each new data entry in my database is a month. Each month I track our church attendance for each service (4-8 services usually). I have a monthly average then.

On my yearly report, I would like to show what our church attendance average was for the whole year.

I have a summary field that shows total attendance for the year, that was easy. So how would I show average attendance for the year? I'm guessing that if I Rounded an average of total attendance, it would just divide that yearly total by number of months and NOT number of services (which is what I want).

So help!

Thanks!!

[CobaltSky]

Each new data entry in my database is a month.

That makes it sound like you have a single record for every month?

Each month I track our church attendance for each service (4-8 services usually). I have a monthly average then.

...And that makes it sound like maybe you are calculating the average and entering it, rather than etnering the attendance and having the database calculate the average?!

But the words you're using are unclear. You don't at any point state what you enter where or how, exactly, the monthly averages are calculated.

On my yearly report, I would like to show what our church attendance average was for the whole year. I have a summary field that shows total attendance for the year, that was easy.

So, if you also have a field (summary or otherwise) showing the total number of services for the year, you can calculate the average service attendance by having FileMaker divide the annual attendance by the number of services. Assuming average service attendance for the year is in fact what you want.

Alternatively, you could have FileMaker calculate the average monthly attendance for the year by dividing the total attendance for the year by 12.

So how would I show average attendance for the year? I'm guessing that if I Rounded an average of total attendance, it would just divide that yearly total by number of months and NOT number of services (which is what I want).

Okay, so it seems you want the average service attendance for the year as a whole?

In which case, as noted above, you need to divide the total attendance for the year (which you have) by the total number of services for the year (it's not clear that you presently have this, or the information from which to arrive at it).

In short, rounded average service attendance for the year is:

Round(TotalAttendanceForYear / NumberOfServicesForYear; n)

One would expect you would approach this by having a services table with a separate record for each service, which could then readily be sub-summarized by month and also by year.

Failing that, however, you can still determine an annual average service attendance by summing the number of services for each month (assuming that has been entered - if not, you will have to add it) and using it as part of the formula mentioned above.

[comment]

I don't see why you need a record for each month. I think you should have a record for each service. From this you can produce a summary report of any period of time, with sub-summaries by month and by year.

[phuebs]

Ok, I guess I need to explain this better.

Here's my setup. Each record is a new month. Each month has about 6 repeating fields: Worship Attendance, Class Attendance, Offerings, Date. I have calculation fields for each month that figure the totals for each and the average Sunday. Filemaker does this just great.

The problems I encounter are in my yearly report.

Each month is listed with a total and average for each category (referenced from each month's record).

At the bottom I have fields that tally the grand totals for each year in attendance, bible class, offerings, etc. Again, no problem because it is a simple summary field with the total of each repeating field.

The issue is trying to create the weekly average over the whole year. I understand how to get it (eg dividing total offerings by number of worship services). But I don't know how to make Filemaker do that off of my repeating fields. It does it great for individual records (or months in my case). But I just don't know how to for the whole year.

The idea has been suggested that I record each Sunday as a record and generate monthly and yearly reports off of that.

However, I'm not sure that A) I want to have 55+ records in this database vs. just 12 (one per month), or : that I would know how to create all the sub-summary and grand total stuff very well.

I'm getting pretty OK at FM, and I read the Bible (FM Bible here) cover to cover, but I don't have a ton of experience using it yet, only a few databases under my belt.

[comment]

The idea has been suggested that I record each Sunday as a record and generate monthly and yearly reports off of that.

Indeed. Although it may be possible to achieve what you want with your current structure, it will be a constant struggle against a self-imposed obstacle.

55+ records in this database vs. just 12

It's still the same amount of data. The division of data into records and fields should be made according to what best serves your purpose in tracking this data.

how to create all the sub-summary and grand total stuff very well.

See if the attached sketch can get you started.

Summary.fp7.zip

[phuebs]

1) Thanks for the sample. It is helpful for many reasons. I am more of a novice than I thought.

2) Through your sample I actually found a great way to make my current database work no problem, which is great because I kind of liked it. I just created a summary field which was a count of church services. Then I edited my "year church attendance average) field and made it a calculation.....total year attendance/church services.

It works great!

Thanks for all the advice, info, and samples

[comment]

That is more or less what I had in mind when I said "it may be possible to achieve what you want with your current structure" (and I believe Ray described a similar approach).

However, you should be aware that this is merely a workaround. Your basic structure is still flawed. You will encounter this again and again, as you try to add more features to your solution. At some point you will run out of workarounds and face a dead end.

[phuebs]

I created a Summary Report. It has a sub-summary with an Average Summary Field per month. How do I get that to be rounded off to an even number, or to two decimal places (I can't decide).

Also, in the same Summary Report, it is Supposed to be organized ascending by year, then by month. But now my February and January got switched around. What happened and how do I fix it?

Thanks

[UpNorth]

To round a number you will want to use the round function when defining the field.

Round(number;precision)

Your help file will explain it further if needed.

As far as your issue with Feb and Jan being switched... try sorting your records again and see if that fixes it.

[phuebs]

I know how to use round, but I don't see how you can Round on a Summary Field. It is no option that I can see or find.

[phuebs]

And I just tried to Sort again, but February still comes first!?!

[comment]

You can format a summary field to DISPLAY as rounded. To really round the result, use a calculation field with the GetSummary() function.

Re your month issue: how is your month field defined?

[phuebs]

I have tried the GetSummary...but I just got an average for the entire year. I wanted a sub summary average for each month AND for the entire year!

My month field is simply a text field with auto-enter to enter the month's name based on the Date field

[UpNorth]

Could you create another field that would round the summary field and use that on your report?

[phuebs]

As I understand, Newbie, that's what a GetSummary Calculation field does. But per my previous two posts, that doesn't help on averaging out monthly totals

[comment]

It seems we are going around in circles.

I have tried the GetSummary...but I just got an average for the entire year. I wanted a sub summary average for each month AND for the entire year!

You will have to use two separate calculations for this, with different breakField parameters each. Read the help on GetSummary() function for more. But I don't see why you need this, if all you want is to display the summary field as rounded. This was covered by Ray in the first response of this thread.

My month field is simply a text field with auto-enter to enter the month's name based on the Date field

Well, the last time I checked, "F" comes before "J" in the English alphabet, so what you are getting is only expected. You need to use the Month() function (with a Number result), rather than the MonthName() function. The demo file I have posted above shows another method to get the month, with a Date result.