This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
Ever since Alteryx 11 came out, the way dates and DateTimes are handled and computed changed from v10. Formulas that I had working before no longer work. The single biggest culprit I tend to see for this problem is that Alteryx 11 no longer seems to be able to intelligently compare Date and DateTime formats. This is kind of annoying because it forces me to run a DateTime function on all my Date fields for doing comparisons.
For example, I have a formula that I use to calculate if a date is the beginning of the month. That formula is:
IF DateTimeTrim([Snapshot Date],"month") = [Snapshot Date]
Where in the above, Snapshot Date is a date field with data incoming in a format like "2017-01-01".
In Alteryx 10, this formula returned as expected, true. However, in Alteryx 11, it returns false. When I dove into this a bit more, I noted that DateTimeTrim will always return a DateTime format, so the formula is attempting to compare "2017-01-01 00:00:00" to "2017-01-01". For some reason, Alteryx now doesn't think this comparison will result to true.
To address this, I now have to do:
IF DateTimeTrim([Snapshot Date],"month") = DateTimeTrim([Snapshot Date], "day")
My suggestion: Let comparisons between Date and DateTime formats work with the assumption that any Date field is as of midnight that day. In the example above, Alteryx would implicitly assume that "2017-01-01" is "2017-01-01 00:00:00" for any comparisons to DateTime, like it did in the past.