You can use GetnthRecord to access the values of fields in specific locations in the current record order:
Let ( R = Get ( RecordNumber ) ;
Case ( R = 1 ; MilesField ;
R = 2 ; ( MilesField + GetNthRecord ( MilesField ; R - 1 ) ) / 2 ;
Sum ( MilesField ; GtNthRecord ( MilesField ; R - 1 ) ; GetNthRecord ( MilesField ; R - 2 ) ) / 3 )
This calculation will only work if the records are present in the current found set and in the correct order.
By any good fortune can you rely on Day 3 being the day after Day 2, Day 2 being the day after Day 1, etc...?
Do you mean that you want the average of the 'previous 3 calendar days', or the 'previous 3 records' (which may not be the same thing)?
I sort of assumed there wasn't going to be a difference. The records would be entered sequentially. Each record would represent a new day, where the previous day would be behind it. In essence, I was trying to do both -previous 3 calendar days and records.
The datastructure would look more like:
ID, dayFormat, miles, 3DayAverage
1, 1/1/11, 4, null
2, 1/2/11, 3.4, null
3, 1/3/11, 2, 3.13
4, 1/4/11, 3, 2.66
I tried your script and got less than optimal results.
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
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.