community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

Formula to assign dates?

Meteor

I wanted to know if there is a certain type of formula that can autofill dates to remove manual manipulation each time I run a workflow. Currently for each new report I run there is a date range that I am manually adjusting in bold

 

IF [DAY]="Monday" THEN "07/11/2016" ELSEIF
[DAY]="Tuesday" THEN "07/12/2016" ELSEIF
[DAY]="Wednesday" THEN "07/13/2016" ELSEIF
[DAY]="Thursday" THEN "07/14/2016" ELSEIF
[DAY]="Friday" THEN "07/15/2016" ELSEIF
[DAY]="Saturday" THEN "07/16/2016" ELSEIF
[DAY]="Sunday" THEN "07/10/2016"
ELSE "" ENDIF

 

If I enter one date for my event start date of 07/12/2016 is there a way to make Alteryx know to change monday to 7/11 and so on? Another caveat to this reports is the dates are always at a future point in time so it's not the current date, I am always going to be about a month ahead of when I work on it..if that makes sense.

 

Thanks in advance for the help!

 

 

Alteryx Certified Partner
Alteryx Certified Partner

Let's start with a table:

 

Monday=0

Tuesday=1

Wednesday=2

Thursday=3

Friday=4

Saturday=5

Sunday=6

 

Today is Thursday, June 16th.

 

I see a record coming in as Friday, so you want me to make that June 16th plus 1 day = June 17th.  The next record is Monday, so you want me to make that June 16th plus 4 days = June 20th.

 

If the day value for today is less than the day value for the day in question, then you want to add the difference (e.g. 3 for Thursday and 4 for Friday).  DateTimeAdd(datetimenow(),4-3, 'days')

 

If the day value for today is greater than or equal to the day value for the day in question, then you want to add 7 minus the difference (e.g. 3 for Thursday and 0 for Monday).  DateTimeAdd(datetimenow(),7-3-0,'days')

 

Is this the right path?

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Meteoroid

I had to do something similar, and what helped me was setting one of the dates as the START_DATE and then using date formulas and calculations to set the remaining dates. Then, you just have to set that start date in one place and the remaining dates will adjust accordingly. 

 

For example

 

  1. Set START_DATE = '7/12/2016' automatically in a workflow step or manually with a formula tool
  2. Create calculations for each day that you need
    • Monday = DateTimeAdd([START_DATE],-1,"days")
    • Tuesday = [START_DATE]
    • Wednesday = DateTimeAdd([START_DATE],-1,"days")

Does this help? 

Quasar
Quasar

You can also set global environment variables for your entire workflow, so if you needed to set some start point that could later be referenced via formula or other kinds of tools, thats another option to consider.

Meteor

Thanks everyone for the insight, it was all super helpful! For what I had set up Bgraves solution seemed to work the best. But good to know other ways of going about it if i have other setups.

 

Thanks again!

Labels