Alteryx Designer

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

Formula Logic - Set Date Range

Highlighted
6 - Meteoroid

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @kherty,

 

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

Highlighted
8 - Asteroid

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

Highlighted
6 - Meteoroid

@Ladarthur  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

 

Highlighted
6 - Meteoroid

@Maskell_Rascal  Thanks!  This works.

 

I appreciate everybody's help here.  

Labels