Calculation of Latest 13 Weeks from Week ID

[sampada0810]

Hi All,

stalwar1‌ -- Please have a look if you can help in this ..

I have a scenario, where I want to get Latest 13 weeks and my data is at week level.

Week Id is in the format 201601..201625..201652.

Currently I am using the logic [where Week ID > Max(Week ID)-13], as of now my Max Week id 201652. So I am able to get latest 13 weeks.

But in future, I can have weeks as 201701..201702.. likewise.

In this case my above logic will fail.

Need a logic to achieve this at SCRIPT LEVEL which will be able to handle future Week IDs too.

[sunny_talwar]

May be like this

FACT:

LOAD [Week ID],

           ....

FROM ....;

Max:

LOAD Max([Week ID]) as MaxWeekID

Resident FACT;

LET vMaxWeekID = Peek('MaxWeekID');

FINAL_FACT

NoConcatenate

LOAD *

RESIDENT FACT

WHERE [WEEK ID] > $(vMaxWeekID) - 13;

DROP Tables FACT, Max;

[aarkay29]

you can try this with little change to your script

Autonumber(WeekID) as WeekSerial

Where WeekSerial > Max(WeekSerial)-13