Discussion Board for collaboration on QlikView Scripting.
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.
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?
Does anyone have a solution for that?
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.
thanks for you answer.
but i need the to do this for all the dates (14 back) and not only for the last one.
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.
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
For CycleDate=num(today()) to vMinDate step -1
Avg(Value) as Avg14,
'$(CycleDate)' as RollingDate
Where Date>$(CycleDate)-14 AND Date<=$(CycleDate)
Group by Asset;
Update: I did not read that this has to be done at the script level. Disregard my post.
You can use RangeAvg(Above()) function to do what you are trying to do.
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.
maxgro I think he wants this to be done in the script.
Within the script:
LET vMinDate = Num(Peek('Date', 13));
LET vMaxDate = Num(Peek('Date', -1));
LET vLoop = $(vMaxDate) - $(vMinDate);
LOAD 0 as Blank
FOR i = 0 to $(vLoop)
LOAD Date($(vMinDate) + $(i)) as Date,
Avg(Close_Value) as [14 Days Avg]
Where Date >= Date($(vMinDate) + $(i) - 13) and Date <= Date($(vMinDate) + $(i))
Group By Asset;
DROP Field Blank;
DROP Table Table;
Resulting Table Box: