topic Create Current Week/Next Week Flag based on Given Date in QlikView

Create Current Week/Next Week Flag based on Given Date

AleRods88 — Wed, 25 Nov 2020 16:16:04 GMT

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!!!

Re: Create Current Week/Next Week Flag based on Given Date

edwin — Fri, 18 Sep 2020 18:29:56 GMT

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

Re: Create Current Week/Next Week Flag based on Given Date

Kushal_Chawda — Fri, 18 Sep 2020 20:07:06 GMT

@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

Re: Create Current Week/Next Week Flag based on Given Date

AleRods88 — Fri, 18 Sep 2020 20:35:46 GMT

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.

Re: Create Current Week/Next Week Flag based on Given Date

Kushal_Chawda — Fri, 18 Sep 2020 20:36:45 GMT

@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