topic Re: 10 Weeks and 39 Weeks rolling Moving Avg in Load Script in QlikView

10 Weeks and 39 Weeks rolling Moving Avg in Load Script

Tue, 08 Dec 2015 07:19:42 GMT

Dear Expert,

i have scripcode wise Week wise sales information. i want load those data to Qlikview Application and also want 10 Weeks rolling moving avg and 39 Weeks rolling moving avg along with that.

i am sharing you a sample excel for ready reference. In Excel i have already added 10week rolling moving Avg and 39 Week rolling moving avg for your reference purpose. which i want to calculate while loading the data.

the sample file contain only one scripcode in original there are many scripcode.

Thanks & Regards,

JKV

Re: 10 Weeks and 39 Weeks rolling Moving Avg in Load Script

Tue, 08 Dec 2015 07:29:35 GMT

One point i forget to add that is i want to consider the missing Week also in the count.

for example if scripcode is not sales in a particular week then also that week will consider in count.

Re: 10 Weeks and 39 Weeks rolling Moving Avg in Load Script

settu_periasamy — Tue, 08 Dec 2015 07:57:10 GMT

Hi,

If you want this in Chart Level, You can try like..

RangeAvg (above(sum(sales),0,10))

and

RangeAvg (above(sum(sales),0,39))

Check the Attachment.

You can refer the below Link also:

Calculating rolling n-period totals, averages or other aggregations

Re: 10 Weeks and 39 Weeks rolling Moving Avg in Load Script

Tue, 08 Dec 2015 08:34:40 GMT

Hi Settu,

Thanks for you reply. i dont want to do that at chart level. because i have to do lots of historical analysis at load level only on the basis of the data.

Re: 10 Weeks and 39 Weeks rolling Moving Avg in Load Script

Tue, 08 Dec 2015 11:17:01 GMT

Hi All,

i have found a better way to do rolling Avg on load script.

1) First i have done Scrip code wise cummlative sum of all Weeks.

2) After that when my Rolling Week count reach i subtract cumulative sum from the 5 record backs cumlative sum and divide by 5 so i get rolling avg of 5 Week.

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