### Title

moving average

### Post

I’m trying to generate a moving average on day’s worth of material as records.

If I have a simple set-up.

day1 2 miles

day2 4 miles

day3 1.4 miles

day4 2 miles

On day5 (for example) I’d like to calculate the moving 3-day (prior) average. I have no clue how to exactly do this. I have ideas, but they seem tougher to pull off in FM versus Excel. Are there shortcuts or functions I missed?

For 'the previous 3 records' you'll have to use PhilModJunk's trick.

For the 'previous 3 Calendar days' you can set up 2 new calculation fields and 2 self-relationships:

Day (your existing Date)

cDayLess1 = Day - 1

cDayLess2 = Day - 2

And:

MileageTable:cDayLess1::selfMileageTableDayLess1:Day

MileageTable:cDayLess2::selfMileageTableDayLess2:Day

Each record in the Mileage Table then has direct access to the mileage data for itself (obviously), and by relationship the mileage for the previous day and the day before. You can calculate the average from those 3 figures. A restriction is it will always be the 3 previous calendar days, not the previous 3 records. A potential upside is it could easily be made flexible to include several entries for each day, which might be useful to you if you are in fact summing (manually) the day's mileage before entering it as 1 record. And you don't have to worry about the calculation depending upon the correct found set and sort. You would be able to directly enter the individual mileages that make up each day's total.

Maybe I haven't the detail right, but I'm sure you get the principle.