add column to calendar that get last 4 weeks

[qliknewbie_1]

Hi,

New to QlikSense and not sure how to go about this and also testing it. Is there a way to create a really simple calendar to use for testing?

Over to my question. I have a calendar where I would like add a column that is a if statement with 1 if its within the last 4 weeks (Whole weeks) up until yesterday (not whole week). I would like this to be for every year and not only the current year. Can someone help me with this.

Just guessed using week and weekstart, but cant get it to work exactly..

Thanks!

[rwunderlich · Accepted solution]

Here's an example.  I'm not sure if you meant to include yesterday if yesterday is the last day the week. I did not include it in this example;

Let vYesterday = num(today(1)-1);
TestCalendar:
Load
  *,
  if(
    Date >= WeekStart(AddYears($(vYesterday), Year(Date)-Year($(vYesterday))),-4)
    and Date <= WeekStart(AddYears ($(vYesterday), Year(Date)-Year($(vYesterday))))
  ,1, '') as Last4Weeks
;
LOAD date(today(1) - RecNo()-1) as Date
AUTOGENERATE today(1) - YearStart(today(1), -3) // Three full years
;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com