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

    Moving Average calculation

    jwong

      Title

      Moving Average calculation

      Post

      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
          comment_1
             You could define a calculation field (result is Date) =

          Date - 4

          and make the self-join:

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



          • 2. Re: Moving Average calculation
            jwong
              

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

             

            Table:

             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
              comment_1
                

              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
                jwong
                  

                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
                  jwong
                    

                  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

                   

                  Price

                  Date

                   

                  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
                    comment_1
                      

                    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().