Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
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_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!!!

1 Solution

Accepted Solutions

@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``````
4 Replies
Master

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
];
``````

@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``````
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.

@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``````
Community Browser