Create Current Week/Next Week Flag based on Given Date

[AleRods88]

Hello,

I have a field named OP_DATE (MM/DD/YYYY) with all Friday dates like this:

OP_DATE

09/04/2020

09/11/2020

09/18/2020

09/25/2020

10/02/2020

10/09/2020

What I need is to create a new field, named WEEK_STATUS, with values: "Current Week", "Next Week", "Previous" and "Rest".

So with this, I would have flagged each OP_DATE, depending if I am standing in the current week (with Today()) or not:

OP_DATE	WEEK_STATUS
9/4/2020	Previous
9/11/2020	Previous
09/18/2020	Current Week
09/25/2020	Next Week
10/2/2020	Rest
10/9/2020	Rest

Note: The "Current Week" status should stay until this Friday inclusive, so the week goes from Monday to Friday.

I'd need to do this by Script and I'll reload the application each day, so the field WEEK_STATUS should change from week to week.

Is there any what to do this?

Thank you!!!

[Oleg_Troyansky]

Hi,

Certainly, this is a common logic that we apply in any Master Calendar load. You build your flags based on the relation between today's date and the date field value. You can use some of the available Date and Time functions, or you can use simple numeric conditions for these flags. For example:

LET vToday = num(Today());

Calendar:

LOAD

    OP_DATE,

    IF($(vToday) - OP_DATE>=0 and $(vToday) - OP_DATE <=5, 'Current Week',

    IF($(vToday) - OP_DATE>=8 and $(vToday) - OP_DATE <=12, 'Previous Week', 'Rest')) as WEEK_STATUS,

...

I'd only make one comment... In my practice, I found it more convenient to work with numeric flags that hold values 0 and 1 - for example, _This_Week_Flag, _Last_Week_Flag, etc... This way, the fields are easy to use in Set Analysis, or they can be used as simple multipliers for your metrics. For example:

This Week Sales = sum(Sales * _This_Week_Flag)

Cheers,

[Kushal_Chawda · Accepted solution]

@AleRods88  try below conditions. assuming you have correct date format to the date field

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