Rolling 4 weeks from a Date

[Anonymous]

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);

[sunny_talwar]

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

[Anonymous]

thanks