Averaging in subsummary in FMP v 7 1

[liqdgld]

I have a report for my surgeries done over tha last 15 years. I get a total number of surgeries and the average per month in the Grand Heading. In the subsummary I get count of surgeries by month and year with sub-totals by year. I have tried to add a calculation to get an average of surgeries done by year and cannot get a formula to work.
Any help would be appreciated.

 

[JeanW]

If you have your total surgeries by year or month in a calc field, make a unstored calc field using the GetSummary() function.

If you want your month average, make an unstored calc field using the Average() function on your month or year GetSummary() result field.

Put that field in a Sub-summary layout part (trailing or leading), sort by month for the month average and sort by year for the year average.

Show the preview of that layout to see the results.

 

[liqdgld]

Thanks for response - the total surgeries is derived as a summary using "count of surgeries" - should I change this to a calculation and if so, which formula should i use?

 

[JeanW]

Well...yes and no.

I was rather thinking about a nice technique that Michael Edoshin used in his cross tab report.

You can read about it in:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html#more

If you put your years in a field, the sort of surgery as sub-category, you can make a very nice perpetual report of all your activities.

Give it a try.
Most developers will jump high and dry when I suggest to use repeating fields, but this is a technique I use for several years to generate reports, based on repeating fields, and it's working very well.

Enjoy

 

[JeanW]

Further to this technique, if you have several pages in preview, it will be hard to scroll through the several records.

Here you can use a very nice technique from Ray Cologon, the floating palette.
You can download a sample file at:

http://www.nightwing.com.au/FileMaker/demos7/demo704.html

If you make a combination from those two techniques your database will look like your surgeries, coooool.

Enjoy and happy filemaking.

If questions feel free...

 

[liqdgld]

I'm sorry to be so stupid about this. My report is set up to show a subsummary count of surgeries by month using summary field of "Count of Surgery" sorting by month and another subsummary showing Count of surgery by year. What I'm having trouble with is creating a calculation to sum the months per year and total years so I can the create the average per year compared to the average per year for the whole 20 years of surgery next to the total surgeries per year.
I'm not sure I'm explaining this well - but all I want to do is to get the average surgeries done per year over the whole time frame.

 

[JeanW]

If you look at the technique from Edoshin (download file), then that would be the way to go, according to me.

It is very simple to implement that technique in an existing application with a minimum of overhead.

You can add as many as records as you want, the result will adapt for the whole period, even for your 20 years and even if you have 100 years.