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

[markgraham123]

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

]

;

[sunny_talwar · Accepted solution]

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

]

;

[Oleg_Troyansky]

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]

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]

Thanks a ton for the help

[markgraham123]

Thanks Oleg!