Statistical Calculations on Summary Fields?

[charper]

Got a problem. I need to be able to perform statistical calculations (Min, Max, and Average) on data that appears in summary fields. This doesn’t appear to be possible in FM. It appears I’ll have to write a script that does these computations from scratch by examining the record data that supports the summary fields - yuck, and I do mean YUCK. I can give more detailed information here if need be, but I’d like to get your general comments on this first.

[LiveOak]

You didn't mention exactly what formulas you wish to use, if they are reasonably standard, you can define multiple summary fields for different types of summaries of a data field. Summary fields can be defined as:

Total of (a field) --> many people assume this is what "Summary field" means and forget the other possibilities

Average of (a field)

Count of (a field)

Minimum of (a field)

Maximum of (a field)

Standard Deviation of (a field)

Fraction of Total of (a field)

-bd

[ June 14, 2001: Message edited by: LiveOak ]

[LiveOak]

You can extract the value from a summary field into a calculation field using the GetSummary() function. Calculation fields have no notion of "Subsummary Part" beyond the GetSummary function used to create them. If 5 records occur before the first subsummary, each will have the value extracted by the GetSummary() function. If you extract the Summary information to a calculation field:

ExtractedTotal (calculation, number) =

GetSummary(SummaryField, BreakField)

AverageField (summary, Average of Extracted Total)

This could work, you are not averaging the say 4 subsummaries, but the ExtractedTotal field times the number of records. This should be the same thing for Min, Max and Average. For example, if your subsummary totals were:

4

18

6

20

Say for 10 total records. The ExtractedTotal field would be populated across 10 records as perhaps:

4

4

4

18

18

6

6

20

20

20

because once calculated the ExtractedTotal field resides in every record. This should not alter the values of Summary fields Ave, Min, and Max defined on ExtractedTotal. I tried it and it seems to work.

-bd

[charper]

I'm sorry, I guess I'm not being clear. I'm well aware of these availability of these statistical summary functions. In fact I use them very heavily in the project in question. Let me start from scratch:

I have a "Sum Total Of" summary field in a subsummary part of my report that breaks on field X. When the report is generated in preview mode, I see the summary field displayed in each break and with a different value in each break just as expected. What I NEED now is the Average, Mininum, and Maximum of the values diaplyed in this one summary field, and then I'd like to display these stats in the Grand Summary (or somewhere at the end of the report).

My first inclination would be to try to define a NEW summary field that operates on my CURRENT summary field described above. Alas, when you go to the summary field definition dialog you'll see that the summary fields you have already defined are not selectable. Drats!!

In short, I need to do summary calculations on summary fields. THAT'S the problem. Sorry for the confusion. Any ideas on how to do this?

[charper]

That works!!! Thank you very much!! Awesome.