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: 
AleRods88
Contributor III
Contributor III

Create Current Week/Next Week Flag based on Given Date

Hello,

I have a table with OP_DAY and OP_DATE (MM/DD/YYYY).

I need to create a new column, named WEEK_STATUS, with this values:

- Previous Week: Based on Today(), previous week (Monday to Sunday). In this case, 9/07/2020 to 9/13/2020.

- Current Week: Based on Today(), this week, from Monday to Sunday

- Next Week: In this case, from 9/21/2020 to 9/27/2020.

Note: The app will reload each day, so the WEEK_STATUS will change from week to week.

OP_DAYOP_DATEWEEK_STATUS
Monday9/7/2020Previous Week
Tuesday9/8/2020Previous Week
Wednesday9/9/2020Previous Week
Thursday9/10/2020Previous Week
Friday9/11/2020Previous Week
Saturday9/12/2020Previous Week
Sunday9/13/2020Previous Week
Monday9/14/2020Current Week
Tuesday9/15/2020Current Week
Wednesday9/16/2020Current Week
Thursday9/17/2020Current Week
Friday9/18/2020Current Week
Saturday9/19/2020Current Week
Sunday9/20/2020Current Week
Monday9/21/2020Next Week
Tuesday9/22/2020Next Week

 

I had the following code, but it's not working properly, because I reloaded today and it shows me next week as it is the current week...

LOAD *,
  if(OP_DATE <= WeekStart(Today(),0,4),'Previous Week',
  if(OP_DATE>=WeekStart(Today(),0,4) and OP_DATE<=WeekEnd(Today(),0,4)+1,'Current Week',
  if(OP_DATE>=WeekStart(Today(),1,4) and OP_DATE<=WeekEnd(Today(),1,4)+1,'Next Week','Rest'))) as Week_Status

 

Is there any way to do that? (By script)

Thank you!!!

1 Solution

Accepted Solutions
Kushal_Chawda

@AleRods88  Yes it is possible just change the last parameter (first week of the day) of both weekstart and weekend function to 5. Basically 0 means Monday, so 5 will be saturday

 if(OP_DATE >= WeekStart(Today(),-1,5) and OP_DATE <= weekend(Today(),-1,5),'Previous Week',
  if(OP_DATE>=WeekStart(Today(),0,5) and OP_DATE<=WeekEnd(Today(),0,5),'Current Week',
  if(OP_DATE>=WeekStart(Today(),1,5) and OP_DATE<=WeekEnd(Today(),1,5),'Next Week','Rest'))) as Week_Status

View solution in original post

4 Replies
edwin
Master II
Master II

try this:

let vThisWeek=WeekStart(today(),0,0);

load OP_DAY, date(OP_DATE) as OP_DATE, WeekStart(OP_DATE,0,0) as Week, 
if(WeekStart(OP_DATE,0,0)=date('$(vThisWeek)'),'Current Week',
	if(WeekStart(OP_DATE,0,0)-date('$(vThisWeek)')=7,'Next Week',
		if(WeekStart(OP_DATE,0,0)-date('$(vThisWeek)')=-7,'Previous Week','otherweek'
		)
	)
) as WeekStat
inline [
OP_DAY,OP_DATE
Thursday,9/3/2020
Friday,9/4/2020
Saturday,9/5/2020
Sunday,9/6/2020
Monday,9/7/2020
Tuesday,9/8/2020
Wednesday,9/9/2020
Thursday,9/10/2020
Friday,9/11/2020
Saturday,9/12/2020
Sunday,9/13/2020
Monday,9/14/2020
Tuesday,9/15/2020
Wednesday,9/16/2020
Thursday,9/17/2020
Friday,9/18/2020
Saturday,9/19/2020
Sunday,9/20/2020
Monday,9/21/2020
Tuesday,9/22/2020
Wednesday,9/23/2020
Thursday,9/24/2020
Friday,9/25/2020
Saturday,9/26/2020
Sunday,9/27/2020
Monday,9/28/2020
Tuesday,9/29/2020
]; 
Kushal_Chawda

@AleRods88  try below code

if(OP_DATE >= WeekStart(Today(),-1,0) and OP_DATE <= weekend(Today(),-1,0),'Previous Week',
  if(OP_DATE>=WeekStart(Today(),0,0) and OP_DATE<=WeekEnd(Today(),0,0),'Current Week',
  if(OP_DATE>=WeekStart(Today(),1,0) and OP_DATE<=WeekEnd(Today(),1,0),'Next Week','Rest'))) as Week_Status
AleRods88
Contributor III
Contributor III
Author

Thank you @Kushal_Chawda 

And is there any way to make each week go from Saturday to Friday?

For example: This Week now would be: sat 12, sun 13, mon 14... to fri 18

Next week: sat 19 to friday 25, and so on.

Kushal_Chawda

@AleRods88  Yes it is possible just change the last parameter (first week of the day) of both weekstart and weekend function to 5. Basically 0 means Monday, so 5 will be saturday

 if(OP_DATE >= WeekStart(Today(),-1,5) and OP_DATE <= weekend(Today(),-1,5),'Previous Week',
  if(OP_DATE>=WeekStart(Today(),0,5) and OP_DATE<=WeekEnd(Today(),0,5),'Current Week',
  if(OP_DATE>=WeekStart(Today(),1,5) and OP_DATE<=WeekEnd(Today(),1,5),'Next Week','Rest'))) as Week_Status