Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Any Idea?
Does anyone have a solution for that?
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.
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
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
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
PFA
maxgro I think he wants this to be done in the script.
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