I need to be able to take a found set of records that have a date field and a number field. From this, I need to create from the number field a "three-week" average field of the number field. The calculation I would have to take the current date and go back three weeks to average out the number. Anyone have any ideas?

Will there be omissions in the dates, or oppsite be several records on one day?

But provided that each day is a new record could the aggregate function be made to Avarage( over a 21 cell repeating calc'field containing this:

Substitute(GetNthRecord ( Extend ( theNumber ) ; 1+Get ( RecordNumber ) -Get ( CalculationRepetitionNumber ));"?";"")

--sd

Yes to both - there could be omissions in the date and multiple records for one day.

I need to be able to take a found set of records

Why don't you constrain this found set to the last three weeks, and use a summary field for the average?

Is it for a stock exchange observations system, where a gliding average is used for sale/purchase decisions?

BTW I played a little with my suggestion, it's important that the repeating calc' is unstored, otherwise won't it adjust to the found set - a refresh issue.

--sd

Yes, the concept is very much the same. User needs to look at a record showing a current number and also an average of that number for the last weeks.

Perhaps a self-join with a three-week criterion, and an average calculation on the related records?

The only problem is that aggregate functions probably are to weak a measure to such a problem, the burden of records could dictate something in this direction:

http://www.fmforums.com/forum/showpost.php?post/278725/

--sd

Thank you all very much for your assistance...I did find a self-join relationship worked.