4 Replies Latest reply on Jan 6, 2011 12:30 AM by Sorbsbuster

    moving average



      moving average


      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?

        • 1. Re: moving average

          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.

          • 2. Re: moving average

            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)?

            • 3. Re: moving average


              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.

              • 4. Re: moving average

                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.