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

IF statement involving DATE

BJSOON
8 - Asteroid

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!

8 REPLIES 8
sumitiiest
8 - 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

 

BJSOON
8 - Asteroid

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')

 

 

afv2688
16 - Nebula
16 - Nebula

Hello @BJSOON ,

 

For that use better the formula

 

DateTimeMonth([DATE_IN])

 

Cheers

BJSOON
8 - Asteroid

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. 

neilgallen
12 - 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)
MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
BJSOON
8 - Asteroid

thank you!  I got the desired results!

BJSOON
8 - Asteroid

thank you!  Let me try this out too.

Labels