ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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!!

Ladarthure
13 - Pulsar
13 - Pulsar

Hi @kherty,

 

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

Maskell_Rascal
12 - Quasar

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