Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
@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
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,
@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