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.

Date	Asset	Open	Close	High	Low	Avg14

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

[ariel_klien]

Any Idea?

[ariel_klien]

Does anyone have a solution for that?

[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.

[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

[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

[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

[maxgro]

PFA

[sunny_talwar]

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

[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