Reports and averages

[mrafs]

Hi,

I'm missing a bit of this filemaker report puzzle for an education database i am building.

I want to generate in a report a yearly figure that is the average attendance of visits per month.

This is a related database "attend_.fp5", all necessary related fields are set field rather than a direct relationship calc so GetSummarys can work.

2x part definitions ('date_month', 'date_year')

Sort by ('date_month', 'date_year')

The visit figure is defined as the result of a

-selfRelate ('SelfID_Date_combi')

-a calc 'cAttendCountIndividual' (1/count(SelfID_Date_combi::@AttendID)

-a total Summary 'SUM_TotalcAttendCountIndividual'

This provides one visit figure per day per individual even if the individual visited several times in one day.

How many months in a year that visits were present is generated in a similar fashion.

-selfRelate ('SelfMonth_year_combi')

-a calc 'cMonthYearCombiCount' (1/count(SelfMonth_year_combi::@AttendID)

-a total Summary 'SUM_TotalcMonthYearCombiCount'

Now to generate the figure I need... If only I could calc (SUM_TotalcAttendCountIndividual'/'SUM_TotalcMonthYearCombiCount) and put it in a report that would work, but it does not.

ideas anyone please......

[CobaltSky]

Hello mrafs,

If you want to use summary fields within calculations, you should explore the uses of the GetSummary( ) function.

Alternatively, what you are trying to achieve could be accomplished by setting up some special purpose relationships (to isolate/filter values by month and year etc) then creating some unstored calculation fields using aggregate functions such as Average( ), Sum( ) and Count( ) etc to generate summary data of the records targetted by these special relationships.

[mrafs]

Thanks Ray

TOP RESULT=01 happy bunny

- I tried G_Sum (again) and found out that you can divide two G_Sum's and place the result on a report.

the answer was:

calc field 'GetSummary(SUM_TotalcAttendCountIndividual, date_year) / GetSummary(SUM_TotalcMonthYearCombiCount, date_year)'

toddlepip,

rafs

[Twizzle]

On 21/10/2003 at 1:16 AM, CobaltSky said:

Hello mrafs,

If you want to use summary fields within calculations, you should explore the uses of the GetSummary( ) function.

 

Alternatively, what you are trying to achieve could be accomplished by setting up some special purpose relationships (to isolate/filter values by month and year etc) then creating some unstored calculation fields using aggregate functions such as Average( ), Sum( ) and Count( ) etc to generate summary data of the records targetted by these special relationships.

Hi Cobalt Sky
I just need some help calculating an average in a Sub Summary Report.

I have a database with shares in it & each of my companies holdings in other companies are recorded there. Now I'm reasonably used to Summary & Grand Summary Totals in an FM report and for me they have usually been a mere Total or Sub Total of a figure but I am having difficulty in getting an average price to appear correctly in a Sub Total.

The Grand Total Average 'share price' appears correctly in my reports but no matter what nomenclature I try I can't get it to appear correctly in the Sub Total. The field 'AverageEntryPrice' is derived by Type: Calculation. Unstored,=Bvalue Total/Total Held. This works well in the Total part of the report but whatever I try in the Sub Total section I can't get it to appear correctly. Currently in the Sub Total section I have "Sub Tot Avge Entry Price" Type: Summary Options: Total of AverageEntryPrice (running with restart), when sorted by Invest Converted::BO.

So this should give me the average price that each of my companies pays for shares in other companies.

Thanks in advance.