6 Replies Latest reply on Jul 20, 2009 12:18 PM by comment_1

    Moving Average calculation



      Moving Average calculation


      I have a table with 2 fields: date and price. I would like to add "moving average" field for the price based on previous 5 days value.  


      I have tried self-join of the table, create a calculation field, but do not know how to define the query to locate previous 5 days price based on the date of each record.


      Any pointers on how to accomplish this using FM 8 will be appreciated. 



        • 1. Re: Moving Average calculation
             You could define a calculation field (result is Date) =

          Date - 4

          and make the self-join:

          YourTable:: cField ≤ YourTable 2:: Date
          YourTable:: Date ≥ YourTable 2:: Date

          • 2. Re: Moving Average calculation

            Thank you for your prompt reply.  I understand the self-join setup. I am still confused in setting up the Moving Average calculation field.


            In the following example, for each day, I need to calculate the average price of previous 4 days.  So, the MA need to lookup price field for each of the 5 days then average. 


            The problem is how to lookup the respective "Price" in the range "cfield" ... "date" ?



             Date                 Price    cfield (=Date-4)            MA (=average of price from (date-4)… date)

            1/5/2009          10        1/1/2009                      =average(10)

            1/6/2009          11        1/2/2009                      =average(111+10)

            1/7/2009          12        1/3/2009                      =average(12+11+10)

            1/8/2009          13        1/4/2009                      =average(13+12+11+10)

            1/9/2009          14        1/5/2009                      =average(14+13+12+11+10)


            • 3. Re: Moving Average calculation

              jwong wrote:
              So, the MA need to lookup price field for each of the 5 days then average. 

              Once you have the relationship in place, all you need is another calculation field (result is Number) =


              Average (  YourTable 2:: Price )

              • 4. Re: Moving Average calculation

                Uggh, stupid me...  I defined the relationship as "test" but setup the calculated field to average over "PDB:price" instead of over "test:price" .. 


                Thank you... Thank you... Thank you..  


                I concur you are a "Super Contributor"   ;-)

                • 5. Re: Moving Average calculation

                  One more question please if you allow...


                  Instead of using date as a range to calculate average, I wish to use the record number as a range. So, I setup two more self-joins named TEST2 and TEST3 respectively.


                  "average_2" is calculated as the average of the first n records which is ok. The "cRecNum" is also correct as each record is numbered sequentially.


                  However, "average_3" won't compute.  Is it because the matched field (cRecNum) is a computed field which is not stored unlike "Date" which is a user entered field?




                  Table  PDB





                  cDate = Date – 4

                  Average_1 = average(TEST::Price)


                  cRecNum = count (TEST2::Price)

                  cRecNum4 = cRecNum - 4

                  Average_2 = average (TEST2::Price)


                  Average_3 = average (TEST3::Price)


                  Date                 Price    cDate               cRecNum         cRecNum4


                  1/5/2009          10        1/1/2009          1                      -3

                  1/6/2009          11        1/2/2009          2                      -2

                  1/7/2009          12        1/3/2009          3                      -1

                  1/8/2009          13        1/4/2009          4                      0

                  1/9/2009          14        1/5/2009          5                      1


                  Self-Join           PDB -- TEST         

                                        cDate <= Date 

                         AND     Date >= Date    


                  Self-Join           PDB -- TEST2       

                                         Date >= Date 


                  Self-Join           PDB -- TEST3       

                                cRecNum4 <= cRecNum 

                   AND     cRecNum >= cRecNum  


                  • 6. Re: Moving Average calculation

                    You cannot have an unstored calculation field on the child side of a relationship.


                    To calculate moving average of 5 consecutive records, based on the current found set and sort order, you don't need a relationship - you can use the GetNthRecord() function to get the previous values directly.


                    See, however, Bug when combining GetNthRecord about using aggregate functions in conjuction with GetNthRecord().