Help with weekly averages

[valnoren]

I have a database in FMP7 that I enter a company's daily stock numbers. Example High, Low, Open, Volume etc.

On another layout I have a portal that shows each days results for a company.

Next to the portal I'd like to show the weekly average in a field for High, Low, Open etc. based on a range of dates that I type in like 02/03/2005...02/08/2005.

I'd appreciate any help

[SlimJim]

Hi valnoren

Can we clarify what exactly you want.

Each week (5 days Monday to Friday?), at the end of the week you calculate various averages for HLOV. Alongside your existing portal for these values you would like another portal showing the weekly averages that lie between two given dates. In your example there is a 5 month range so there will be 21-22 weeks (actual number irrelevant here) so your portal will show 21-22 numbers for these averages. Is that correct?

[comment]

Slim:

Across the pond, 02/03/2005...02/08/2005 is a week. Well, six days anyway.

Valnoren:

In the Companies table, define 2 global date fields, gStartDate and gEndDate.

Define another relationship to daily quotes:

Company::CompanyID = Quotes 2::CompanyID

AND

Company::gStartDate <= Quotes 2::Date

AND

Company::gEndDate >= Quotes 2::Date

In the Companies table, define a calculation field (result is number) =

Average ( Quotes 2::High )

Define more averaging fields as needed.

[SlimJim]

:blush: Who's idea was it to have different date formats? Answers please on a postcard but not to me.

[valnoren]

Thanks a ton I will give that a try.