Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ariel_klien
Specialist
Specialist

Avg for the last 14 Days

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

10 Replies
maxgro
MVP
MVP

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;