Three Week Average of Number

[bonngo]

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?

[Søren Dyhr]

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

[bonngo]

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

[comment]

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?

[Søren Dyhr]

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

[bonngo]

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.

[T-Square]

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

[Søren Dyhr]

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

[bonngo]

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