topic Re: Rolling 4 weeks from a Date in QlikView

Rolling 4 weeks from a Date

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a Field YearWeek having '.....201650,201651,201652,201701' as YearWeek . I need to create my data on basis of Rolling four weeks . Since this week is 201701 when i do max(YearWeek) - 3 and create 4 weeeks i get '201700,201799,201798,201797'

But O/p i need is :- '201652,201651,201650,201649'.

code i used:-

MaxYearWeekRTF:

LOAD max(YearWeek) as YearWeek

Resident YearWeekRTF;

let vSPSWeek = Peek('YearWeek',0, 'MaxYearWeekRTF');

DROP Table MaxYearWeekRTF;

let vSPSWeekStart = '$(vSPSWeek)'-3;

Dates: // rolling 4 weeks

LOAD $(vSPSWeekStart) + IterNo() - 1 as YearWeek

AutoGenerate 1

While $(vSPSWeekStart) + IterNo() - 1 <=$(vSPSWeek);

Re: Rolling 4 weeks from a Date

sunny_talwar — Wed, 04 Jan 2017 10:04:07 GMT

May be use MakeWeekDate() function to convert your number into a date and then you can easily go back

MakeWeekDate(Left(YearWeek, 4), Right(YearWeek, 2)) as Date

and then may be instead of going back 1, you can go back 7 days

MaxYearWeekRTF:

LOAD max(Date) as MaxDate

Resident YearWeekRTF;

LET vSPSWeek = Peek('MaxDate',0, 'MaxYearWeekRTF');

DROP Table MaxYearWeekRTF;

LET vSPSWeekStart = '$(vSPSWeek)'-21;

Dates: // rolling 4 weeks

LOAD $(vSPSWeekStart) + 7*(IterNo() - 1) as YearWeek

AutoGenerate 1

While $(vSPSWeekStart) + 7*(IterNo() - 1) <=$(vSPSWeek);

I have not tested this, but you get the idea, I guess

Re: Rolling 4 weeks from a Date

Anonymous — Mon, 16 Jan 2017 11:55:50 GMT

thanks