10 Replies Latest reply: Mar 12, 2015 6:42 PM by Massimo Grossi RSS

    Avg for the last 14 Days

    Ariel Klien

      Hello all Experts.

       

      I have a table with the columns : Date, Asset, Open, Close ,High. Low .

      in the table I ave more then 200 Asset.

      for each asset I have year of daily data.

      DateAssetOpenCloseHighLowAvg14

       

      I need for each row of date include avg(Close) for the last 14 days of the asset.

       

      I need to do this on the script level.

       

      Does anyone have an Idea?

       

      Ariel

        • Re: Avg for the last 14 Days
          Ariel Klien

          Any Idea?

          • Re: Avg for the last 14 Days
            Ariel Klien

            Does anyone have a solution for that?

            • Re: Avg for the last 14 Days
              Peter Turner

              Hello Ariel,

               

              You’d need to use this sort of idea

               

              New_Table:
              Load
              Asset,
              Avg(Close) as Avg14
              Resident Your_Data_Table
              Where Date>today()-14
              Group by Asset;

               

              You need to define a subset of your data with the Where Date>today()-14 ,then use that subset to base you average function on.

              This will create a new table that is linked back to your main table using the key field Asset.

                • Re: Avg for the last 14 Days
                  Ariel Klien

                  Hi Peter,

                   

                  thanks for you answer.

                   

                  but i need the to do this for all the dates (14 back) and not only for the last one.

                   

                  BR

                   

                  Ariel

                    • Re: Avg for the last 14 Days
                      Peter Turner

                      Hello Ariel,

                       

                      So for a rolling 14day average you'd need to add afew more lines of script and create a loop over them.

                      For example below i made some test data in my Data table

                      Set a variable so i know what the first date in the range is

                      Create a loop over each date from today back to the first date in the range, and work its way backwards.

                      Then to create your 14 day subset of data i used the where condition based on the date it was looking at, hope that helps / points you in the right direction.

                       

                      Data:

                      LOAD * INLINE [

                          Date, Value, Asset

                          01/01/2015, 9, A

                          02/01/2015, 8, A

                          03/01/2015, 7, A

                          04/01/2015, 6, A

                          05/01/2015, 5, A

                          06/01/2015, 4, A

                          07/01/2015, 3, A

                          08/01/2015, 2, A

                          09/01/2015, 1, A

                          10/01/2015, 9, A

                          11/01/2015, 8, A

                          12/01/2015, 7, A

                          13/01/2015, 6, A

                          14/01/2015, 5, A

                          15/01/2015, 4, A

                          16/01/2015, 3, A

                          17/01/2015, 2, A

                          18/01/2015, 1, A

                          19/01/2015, 9, A

                          20/01/2015, 8, A

                      ];

                       

                       

                      LET vMinDate=num(peek('Date',0,'Data'));

                       

                      For CycleDate=num(today()) to vMinDate step -1

                       

                      New_Table:

                      Load

                      Asset,

                      Avg(Value) as Avg14,

                      '$(CycleDate)' as RollingDate

                      Resident Data

                      Where Date>$(CycleDate)-14 AND Date<=$(CycleDate)

                      Group by Asset;

                       

                      Next CycleDate

                  • Re: Avg for the last 14 Days
                    Sunny Talwar

                    Update: I did not read that this has to be done at the script level. Disregard my post.

                     

                    Best,

                    S


                    You can use RangeAvg(Above()) function to do what you are trying to do.

                     

                    Script:

                     

                    Data:

                    LOAD * INLINE [

                        Date, Open_Value, Close_Value, Asset

                        01/01/2015, 9, 10, A

                        01/02/2015, 8, 7, A

                        01/03/2015, 7, 6, A

                        01/04/2015, 6, 10, A

                        01/05/2015, 5, 4, A

                        01/06/2015, 4, 12, A

                        01/07/2015, 3, 2, A

                        01/08/2015, 2, 6, A

                        01/09/2015, 1, 5, A

                        01/10/2015, 9, 2, A

                        01/11/2015, 8, 4, A

                        01/12/2015, 7, 6, A

                        01/13/2015, 6, 5, A

                        01/14/2015, 5, 10, A

                        01/15/2015, 4, 12, A

                        01/16/2015, 3, 2, A

                        01/17/2015, 2, 6, A

                        01/18/2015, 1, 5, A

                        01/19/2015, 9, 4, A

                        01/20/2015, 8, 8, A

                    ];

                     

                    Expression for 14 Day Avg:

                    =If(RowNo() >= 14, Num(RangeAvg(Above(Close_Value,0,14)), '#,##0.00'))

                     

                    Output Table would look something like this:

                     

                     

                    Also attaching the sample app.

                     

                    HTH

                     

                    Best,

                    S

                      • Re: Avg for the last 14 Days
                        Sunny Talwar

                        Within the script:

                         

                        Data:

                        LOAD * INLINE [

                            Date, Open_Value, Close_Value, Asset

                            01/01/2015, 9, 10, A

                            01/02/2015, 8, 7, A

                            01/03/2015, 7, 6, A

                            01/04/2015, 6, 10, A

                            01/05/2015, 5, 4, A

                            01/06/2015, 4, 12, A

                            01/07/2015, 3, 2, A

                            01/08/2015, 2, 6, A

                            01/09/2015, 1, 5, A

                            01/10/2015, 9, 2, A

                            01/11/2015, 8, 4, A

                            01/12/2015, 7, 6, A

                            01/13/2015, 6, 5, A

                            01/14/2015, 5, 10, A

                            01/15/2015, 4, 12, A

                            01/16/2015, 3, 2, A

                            01/17/2015, 2, 6, A

                            01/18/2015, 1, 5, A

                            01/19/2015, 9, 4, A

                            01/20/2015, 8, 8, A

                        ];

                         

                        LET vMinDate = Num(Peek('Date', 13));

                        LET vMaxDate = Num(Peek('Date', -1));

                        LET vLoop = $(vMaxDate) - $(vMinDate);

                         

                        Table:

                        LOAD 0 as Blank

                        AutoGenerate 1;

                         

                        FOR i = 0 to $(vLoop)

                          Concatenate (Table)

                          LOAD Date($(vMinDate) + $(i)) as Date,

                          Asset,

                          Avg(Close_Value) as [14 Days Avg]

                          Resident Data

                          Where Date >= Date($(vMinDate) + $(i) - 13) and Date <= Date($(vMinDate) + $(i))

                          Group By Asset;

                         

                        NEXT

                         

                        DROP Field Blank;


                        Join(Data)

                        LOAD *

                        Resident Table;

                         

                        DROP Table Table;


                        Resulting Table Box:


                         

                        HTH

                         

                        Best,

                        S

                      • Re: Avg for the last 14 Days
                        Massimo Grossi

                        PFA

                          • Re: Avg for the last 14 Days
                            Sunny Talwar

                            Massimo I think he wants this to be done in the script.

                              • Re: Avg for the last 14 Days
                                Massimo Grossi

                                try this

                                 

                                // test data, 1 year, 200 asset

                                Tmp:

                                load 'Asset ' & num(rowno(), '000') as Asset2 AutoGenerate 200;

                                join (Tmp) load date(makedate(2015) + rowno()-1) as Date2 autogenerate 365;

                                Asset2: NoConcatenate load Asset2, Date2, floor(rand()*10+1) as Value2 Resident Tmp; 

                                DROP Table Tmp;

                                 

                                // load order by, peek to get previous 13 values

                                Final2:

                                load *,

                                  rangeavg(

                                  Value2,

                                  if(Peek(Asset2)=Asset2, Peek(Value2)) ,

                                  if(Peek(Asset2,-2)=Asset2, Peek(Value2,-2)) ,

                                  if(Peek(Asset2,-3)=Asset2, Peek(Value2,-3)) ,

                                  if(Peek(Asset2,-4)=Asset2, Peek(Value2,-4)) ,

                                  if(Peek(Asset2,-5)=Asset2, Peek(Value2,-5)) ,

                                  if(Peek(Asset2,-6)=Asset2, Peek(Value2,-6)) ,

                                  if(Peek(Asset2,-7)=Asset2, Peek(Value2,-7)) ,

                                  if(Peek(Asset2,-8)=Asset2, Peek(Value2,-8)) ,

                                  if(Peek(Asset2,-9)=Asset2, Peek(Value2,-9)) ,

                                  if(Peek(Asset2,-10)=Asset2, Peek(Value2,-10)) ,

                                  if(Peek(Asset2,-11)=Asset2, Peek(Value2,-11)) ,

                                  if(Peek(Asset2,-12)=Asset2, Peek(Value2,-12)) ,

                                  if(Peek(Asset2,-13)=Asset2, Peek(Value2,-13))

                                  ) as AvgValue2 ,

                                  rangesum(

                                  Value2,

                                  if(Peek(Asset2)=Asset2, Peek(Value2)) ,

                                  if(Peek(Asset2,-2)=Asset2, Peek(Value2,-2)) ,

                                  if(Peek(Asset2,-3)=Asset2, Peek(Value2,-3)) ,

                                  if(Peek(Asset2,-4)=Asset2, Peek(Value2,-4)) ,

                                  if(Peek(Asset2,-5)=Asset2, Peek(Value2,-5)) ,

                                  if(Peek(Asset2,-6)=Asset2, Peek(Value2,-6)) ,

                                  if(Peek(Asset2,-7)=Asset2, Peek(Value2,-7)) ,

                                  if(Peek(Asset2,-8)=Asset2, Peek(Value2,-8)) ,

                                  if(Peek(Asset2,-9)=Asset2, Peek(Value2,-9)) ,

                                  if(Peek(Asset2,-10)=Asset2, Peek(Value2,-10)) ,

                                  if(Peek(Asset2,-11)=Asset2, Peek(Value2,-11)) ,

                                  if(Peek(Asset2,-12)=Asset2, Peek(Value2,-12)) ,

                                  if(Peek(Asset2,-13)=Asset2, Peek(Value2,-13))

                                  ) as SumValue2

                                Resident Asset2

                                order by Asset2, Date2;

                                 

                                DROP Table Asset2;