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

I need a formula to give me records with past dates up to 7 days ago. Can you help?

Marcegon
8 - Asteroid

I am using the following formula but it gives me last week's records only:

DATETIMEPARSE([End_date], "%Y-%m-%d") >= DATETIMEADD(DATETIMENOW(), -7, "days")

 

Also, I need a formula that gives me records with past dates and + 14 future dates.  The formula below is not giving me past dates:

DATETIMEPARSE([Start_date], "%Y-%m-%d") >= DATETIMEADD(DATETIMENOW(), +14, "days")

 

Thank you for your help!

5 REPLIES 5
johnyli168
Alteryx
Alteryx

Hey @Marcegon ,

You could look to add another "AND" statement to catch the other side of the +/- days. 

I have attached an example with some dummy data.

 

IF [EXAMPLE DATE] >= DATETIMEADD(DATETIMENOW(), -7, "days") AND [EXAMPLE DATE] <= DATETIMEADD(DATETIMENOW(), +7, "days") THEN "Yes" ELSE "No" ENDIF

 

johnyli168_0-1660183243823.png

 

 

SPetrie
12 - Quasar

Correct me if I'm misunderstanding the ask, but it sounds like you only want dates that are 7 days old or older.

The formula you have is saying you only want dates that come on or after 7 days ago. Saying a date is greater means it comes after the test date. 

SPetrie_0-1660184233924.png

You want to switch to <= so that its saying you want dates the came before your test date.

SPetrie_1-1660184325305.png

 

Same with the other formula, switching to <= will give you all dates that are up to 14 days after the current date.

 

SPetrie_3-1660184709671.png

 

 

 

 

Marcegon
8 - Asteroid

Hi, the formula kind of fixed the issue because I am getting past records, but I am getting records beyond (2) weeks as well.  I am not sure what is wrong with the rule (see rule and output below)

 

IF [Actual format] = "Virtual" OR [Actual venue type] = "Virtual" THEN "Not required" ELSEIF DATETIMEPARSE([Start_date], "%Y-%m-%d") <= DATETIMEADD(DATETIMENOW(), +14, "days") AND IsEmpty([Event Location]) OR [Event Location] = "TBD" THEN "0" Else [Event Location] ENDIF

 

OUTPUT - I don't want to enter a "0" for a record with a date beyond 14 days.

Start_dateEnd_dateFiscal YearEvent Location
9/14/2022 17:009/14/2022 19:00FY220

 

Any help would be much appreciated.  Thanks!

SPetrie
12 - Quasar

Can you supply some example input and expected output for this formula?

The example here has a different date format than what you are parsing from in the formula and since event location is already filled in, there is no way to really tell what the formula was acting upon.

Having a better sample set of input and expected output would be helpful.

If you are able to attach your workflow, that would be even more helpful so we could see what other tools are in play that may affect how the fields are being populated.

Marcegon
8 - Asteroid

It worked.  Thank you so much!  It looks like something else was wrong.

 

Labels