Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
markgraham123
Specialist
Specialist

Get last week and next week's date using the available date

Hi all,

Here is the data i have:

For every date, i'm trying to get 2 new date fields:

For all the dates between 2/5/2016 to 2/11/2016....  I want 2/19/2016 as deadline, 1/22/2015 as start date

similarly, 2/12/2016 to 2/18/2016 will have 2/26/2016 is deadline and 2/5/2016 is start date

and

2/19/2016 to 2/25/2016 will have 3/4/2016 as deadline and 2/12 will be start date.

Can someone pls help.

Eg: For 2/6/2015, i need 1/29/2015,

Table1:

LOAD * Inline

[

2/5/2016

2/6/2016

2/7/2016

2/8/2016

2/9/2016

2/10/2016

2/11/2016

2/12/2016

2/13/2016

2/14/2016

2/15/2016

2/16/2016

2/17/2016

2/18/2016

2/19/2016

2/20/2016

2/21/2016

2/22/2016

2/23/2016

]

;

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Table1:

LOAD Date,

  WeekEnd(Date+1, 1, 5) as Deadline,

  WeekStart(Date, -2, 4) as StartDate;

LOAD * Inline

[

Date

2/5/2016

2/6/2016

2/7/2016

2/8/2016

2/9/2016

2/10/2016

2/11/2016

2/12/2016

2/13/2016

2/14/2016

2/15/2016

2/16/2016

2/17/2016

2/18/2016

2/19/2016

2/20/2016

2/21/2016

2/22/2016

2/23/2016

]

;


Capture.PNG

View solution in original post

6 Replies
sunny_talwar

Try this:

Table1:

LOAD Date,

  WeekEnd(Date+1, 1, 5) as Deadline,

  WeekStart(Date, -2, 4) as StartDate;

LOAD * Inline

[

Date

2/5/2016

2/6/2016

2/7/2016

2/8/2016

2/9/2016

2/10/2016

2/11/2016

2/12/2016

2/13/2016

2/14/2016

2/15/2016

2/16/2016

2/17/2016

2/18/2016

2/19/2016

2/20/2016

2/21/2016

2/22/2016

2/23/2016

]

;


Capture.PNG

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Mark,

I suggest to look at the functions WeekStart and WeekEnd - notice the option parameters shift and weekoffset that allow you to move a certain number of weeks up or down, and move the first day of the week.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

markgraham123
Specialist
Specialist
Author

I'm assuming u as a magician my friend.

Can i please know what does

WeekEnd(Date+1, 1, 5)

  WeekStart(Date, -2, 4)   do...


I'm aware of WeekEnd and WeekStart function...

Cna u pls explain the above two lines my friend!

sunny_talwar

Like Oleg‌ mentioned below WeekStart and WeekEnd can be shifted + offset by using its 2nd and 3rd parameter, respectively. I just played around with those parameters to get you what you were looking for. No magic at all

weekend ‒ QlikView

weekstart ‒ QlikView

I think I have got the order incorrect above

2nd parameter is WeekOffset

3rd paramter is shift the weekdays

markgraham123
Specialist
Specialist
Author

Thanks a ton for the help

markgraham123
Specialist
Specialist
Author

Thanks Oleg!