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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

IF statement involving DATE

Meteoroid

Hi,

I'm trying to add a column to indicate whether the "Effective Date" is in the current or future month (i.e. TRUE) or previous month (i.e. FALSE), using last day of previous month's date in the formula.  The Excel formula looks like this:

 

=IF(M2>DATEVALUE("10/31/18"),TRUE,FALSE)

 

I want to:

1. set the date value to always check for last day of previous month instead of having to change it each time the report is run in Alteryx.

2. find out how to convert the Excel formula (bearing in mind point 1) in Alteryx?

 

Thank you!

Asteroid

Hi @BJSOON ,

 

Please find attached the alteryx workflow for your requirement.

I have used the below formula to get last day of previous month.

date calc.PNG

 

Thanks,

Sumit

 

Meteoroid

Hi Sumit, this works!  Thank you so much!  I have another question on date formula.  I tried the following formula to get all the June records, however, it keep missing the 1st and 2nd June records which exist in my input file.  It only returned records for 3rd June and onwards.  What did I miss? 

 

DateTimeAdd(DateTimeFirstOfMonth(),-0,'months')

 

 

Alteryx Partner

Hello @BJSOON ,

 

For that use better the formula

 

DateTimeMonth([DATE_IN])

 

Cheers

Meteoroid

Thanks.  How do I formulate it to check for 1st of every month?  For e.g. when I run the report any time in June, I want the results to return 1st June onwards.  When I run the report any time in July, I want the results to return 1st July onwards. 

Quasar
datetimemonth([date in]) = datetimemonth(datetimetoday())

I’m not clear on the scope of your data but you may want to include a year component as well (just swap out month for year in the statement above)
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
DateTimeStart() is when you ran.

First of month could be expressed as:

DateTimeFormat(DateTimeStart(),"%Y-%m-01")

Cheers,

Mark
Alteryx ACE & Top Community Contributor

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

thank you!  I got the desired results!

Meteoroid

thank you!  Let me try this out too.

Labels