Sub-Summary Report Group Count Problem

[alanf]

I am having a problem finding the correct way to calculate a simple average. Here is my situation...

I have a report that has a Sub-summary break on Year and a Sub-Summary break on Month.

I am able to correctly show by month and year the following data:

New Sales Units

Used Sales Units

Total Units

% new

% used

What I can not seem to do is get the average New Sales Units or Average Used Sales Units if the report is run for multiple years of data (2006, 2007, 2008). Report is sorted by Year of Sale and Month of Sale.

Example: Year 2006 has 6 months of data

If new sales =72 then average monthly new sales should be 72/6=12

I could not find a direct way to count the number of sub-summary breaks in order to count the months in a given years data set. John Mark Osborne at database pro's had a solution which I used to count unique records via serial numbers (in this case months) and I was able to show the number of months on the report. But when trying to use the number of months in a calculation It appears to only use the count for the entire report data set found over all years and not the individual year break total.

I have attached 2 reports to see the problem

Thanks for any help on this!

MultiYear_Report.pdf

2006_Report.pdf

[comment]

I am not quite sure how you calculate the number of months, but I believe you can get the average you seek by:

GetSummary ( sTotal ; cYear ) / Round ( sCountMonths ; 0 )

That's for the average month in a year sub-summary. For the grand total:

sTotal / Round ( GetSummary ( sCountMonths ; sCountMonths ) ; 0 )

[alanf]

Thanks for pointing me in the right direction. I was able to get the following to work by creating 2 new calculation fields.

For the Yearly Sub Summary Break:

Total Units Average Yearly = GetSummary(Total Units Summary;Year of Sale)/GetSummary(Unique Month Total;Year of Sale)

For the Trailing Grand Summary Break:

Total Units Average Grand Total = Total Units Summary/Unique Month Total

I still do not understand why I have to ask filemaker to calculate with a getsummary function within a sub-summary break. I need to understand why this is so and why Filemaker does not internally handle common calculation needs such as this.

I am new to FMpro and coming from a ms-access world where report calculations are defined at the report level instead of having to predefine many calculations through field definition at the table level. It's got me flustered!

Thanks for your help. Your guidance solved it.

Edited January 23, 2008 by Guest

[comment]

I still do not understand why I have to ask filemaker to calculate with a getsummary function within a sub-summary break. I need to understand why this is so and why Filemaker does not internally handle common calculation needs such as this.

I am not sure I understand this well enough myself to explain it, but it has to do with summary fields returning multiple results according to the layout part they are placed in. Calculation fields, OTOH, also return multiple results, but on a record-by-record basis. A calculation field placed on a summarizing part takes its results from the last record in the group (or the first, if the part is leading).

Therefore, if you are using a summary field in a calculation, and you want a partial result for a particular group (out of the many groups a record can belong to), you need to tell the calculation explicitly which breakField to use.