Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
qliknewbie_1
Contributor II
Contributor II

add column to calendar that get last 4 weeks

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!

 

Labels (2)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

1 Reply
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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