How to calculate Rolling Moving Avg on Load Script

Tue, 08 Dec 2015 11:30:30 GMT

Dear All,

i have found lots of people sharing different different concept to achieve rolling Moving Avg on load script.

Here is a simple way to create Rolling Moving Average on Load Script

i have Scrip code wise Weekly Close Rate table. i needs to create 5 Week rolling moving Avg on Close Rate.

Step 1.

First simple create a Scrip code , Weekly Cumulative sum of Close Rate :

load [Scrip Code],

TradingWeekSeqNo,

[Close Rate],

if( [Scrip Code]=Peek([Scrip Code]),RangeSum([Close Rate],peek([Cumm Close Rate])),[Close Rate]) as [Cumm Close Rate]

Resident tbl_Weeklytrade order by [Scrip Code],TradingWeekSeqNo

this will calculate Scrip code wise Weekly Cumulative Sum of Close Rate.

Step 2.

After that add preceding load like this

load *,

if([Scrip Code]=Peek([Scrip Code]) and TradingWeekSeqNo>5,([Cumm Close Rate]-Peek([Cumm Close Rate],-5))/5,[Cumm Close Rate]/TradingWeekSeqNo ) as Rolling ;

here what i am doing is checking when my WeekSeqNo go beyond my rolling Moving Average, i subtract current Week Cumulative Sum of Close Rate with previous 5 week Cumulative Close rate and divide by 5 else divided just cumulative Close rate by simple No of Weeks .

Whole Syntax is like below

tbl_Cumm:

load *,

if([Scrip Code]=Peek([Scrip Code]) and TradingWeekSeqNo>5,([Cumm Close Rate]-Peek([Cumm Close Rate],-5))/5,[Cumm Close Rate]/TradingWeekSeqNo ) as Rolling ;

load [Scrip Code],

TradingWeekSeqNo,

[Close Rate],

if( [Scrip Code]=Peek([Scrip Code]),RangeSum([Close Rate],peek([Cumm Close Rate])),[Close Rate]) as [Cumm Close Rate]

Resident tbl_Weeklytrade where [Scrip Code]>=500000 and [Scrip Code]<=599999 order by [Scrip Code],TradingWeekSeqNo.

Thanks & Regards,

Jitendra Kumar Vishwakarma

Re: How to calculate Rolling Moving Avg on Load Script

sinanozdemir — Tue, 08 Dec 2015 15:49:24 GMT

I think it would be better if you can post a sample data-set.

topic How to calculate Rolling Moving Avg on Load Script in QlikView

How to calculate Rolling Moving Avg on Load Script

Re: How to calculate Rolling Moving Avg on Load Script