Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Formula Logic - Set Date Range

kherty
7 - Meteor

I apologize if this has been answered in the past, but I cannot seem to find a good way to automate this formula tool.  I run a report M-F of the current week and always want to provide date ranges for prior week and next week.

 

If [Date] >= "2020-05-24" and [date] <= "2020-05-30" then "NEXT WEEK"
elseif [Date] >= "2020-05-17" and [date] <= "2020-05-23" then "CURRENT WEEK"
elseif [Date] >= "2020-05-10" and [date] <= "2020-05-16" then "LAST WEEK"
elseif [Date] >= "2020-05-03" and [date] <= "2020-05-09" then "PRIOR WEEK"
else Null()
endif

 

Is there a way to use DateTimeToday() so I don't have to manually roll these dates each Monday?

 

Thanks!!

4 REPLIES 4
Ladarthure
14 - Magnetar
14 - Magnetar

Hi @kherty,

 

to do so, I wouyld use the week number and compare to the "today", here is a sample workflow

Maskell_Rascal
13 - Pulsar

Hi @kherty 

 

The attached should work for you. I'm calculating the week start date for both the Date column and for the current week. You can then use a DateTimeDiff formula to calculate the difference in days between each week's start date and call out the appropriate week identifier.

Maskell_Rascal_0-1589811057246.png

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Thanks!

Phil

kherty
7 - Meteor

@Ladarthure  Thanks for this!  I tried it and it appears to work well.  It did drop the Sunday of last week though.  Any idea of how I could get this included?

 

kherty_0-1589812442605.png

 

kherty
7 - Meteor

@Maskell_Rascal  Thanks!  This works.

 

I appreciate everybody's help here.  

Labels