Create Current Week/Next Week Flag based on Given Date

[AleRods88]

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_DAY	OP_DATE	WEEK_STATUS
Monday	9/7/2020	Previous Week
Tuesday	9/8/2020	Previous Week
Wednesday	9/9/2020	Previous Week
Thursday	9/10/2020	Previous Week
Friday	9/11/2020	Previous Week
Saturday	9/12/2020	Previous Week
Sunday	9/13/2020	Previous Week
Monday	9/14/2020	Current Week
Tuesday	9/15/2020	Current Week
Wednesday	9/16/2020	Current Week
Thursday	9/17/2020	Current Week
Friday	9/18/2020	Current Week
Saturday	9/19/2020	Current Week
Sunday	9/20/2020	Current Week
Monday	9/21/2020	Next Week
Tuesday	9/22/2020	Next 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!!!

[edwin]

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]

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 · Accepted solution]

@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