Skip to main content
Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
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 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_DATEWEEK_STATUS
9/4/2020Previous
9/11/2020Previous
09/18/2020Current Week
09/25/2020Next Week
10/2/2020Rest
10/9/2020Rest

 

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

1 Solution

Accepted Solutions
Kushal_Chawda

@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

 

View solution in original post

2 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

@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