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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
SOLVED

Date formula

I have 2 date columns in my data, [RISK DATE] and [Current Mth formatted].  I have converted both of these dates to be Strings in the format Mon-yy.  I need a formula that compares these dates and identifies if the risk date is equal to, after (in a calendar year) or before the current month.

 

This is the formula, I have tried but does not seem to be working.

 

IF [RISK DATE]=[Current Mth formatted] THEN "Future CM Realized" ELSEIF [RISK DATE]<[Current Mth formatted] THEN "Future PM Realized" ELSEIF [RISK DATE]>[Current Mth formatted] THEN "Future FM Unrealized" ELSE "INVESTIGATE" ENDIF

 

FYI the Future refers to a type of derivative and the CM = "Current Month" PM = "Prior Month" and FM = "Future Month"

 

The results I'm getting with the above formula have a Risk Date of Mar-19, Current Mth formatted of Apr-19 but are assigning this "Future FM Unrealized" when it should be that March is less than or before April.

 

I'm new to working with dates in formulas.  The Risk Date comes in the format of yyyyMM which I converted to a string Mon-yy.

 

Any suggestions?

 

Alteryx Certified Partner
Alteryx Certified Partner
Hi!

In order to do an accurate comparison of dates you need to convert your data type to be of date type, which in Alteryx is in the format YYYY-MM-DD.

If there is no day in your data, then just add 01 so each month is given as the first.

At present your comparison wi be made as a string and thus alphabetically when doing a greater than.

There is some optimisation to do with the if statement but let's create our date fields first!

I would do something like (on your original date format)...


Left[field],4)+'-'+substring([field],5,2)+'-01'

In a formula tool, and again making sure you specify the output data type as date!

Ben
Highlighted

Thank you for explaining why I was getting the results that I was.  I thought I was going crazy because it seemed like one month it worked and the next it didn't, but now that I know it was looking alphabetically that it makes sense.  Very helpful and easy to apply!

 

Thanks again!

Labels