Need help converting a Microsoft Access query in Alteryx

Greetings,

I am working on replacing MS Access based functions / queries in Alteryx workflow, trying to replace below month count difference in Filter function in Alteryx, but getting errors, can someone pls help

((DateDiff("m",[Incidents].[Creation Date],[Cob].[Rundate])+1)<=6))

Basically I want to make sure I am selecting data for Creation Date month <=6. For e.g. if Creation month is Aug, then I need to select records from Incidents table for which the month value is <= 6 than the Creation Date from Incidents table and RunDate from CoB table.

Thanks,

kishore Karankal

Date Time

Accepted answers

ShankerV

Hi @kishorhk

I assume both the columns are in the same dataset.

Please use the below formula to filter.

DateTimeDiff([Creation date],[Rundate],"months")<=6

Note: Also the datetimediff function will work only if the dates are in the ISO format which Alteryx recognizes as date YYYY-MM-DD.

If not you need to additionally use datetime tool or datetimeparse function to convert the dates to ISO format.

Many thanks

Shanker V

All comments

ShankerV

Hi @kishorhk

I assume both the columns are in the same dataset.

Please use the below formula to filter.

DateTimeDiff([Creation date],[Rundate],"months")<=6

Note: Also the datetimediff function will work only if the dates are in the ISO format which Alteryx recognizes as date YYYY-MM-DD.

If not you need to additionally use datetime tool or datetimeparse function to convert the dates to ISO format.

Many thanks

Shanker V

kishorhk

Thanks Shankar, it helped, may I know how the +1 in month count can be added as well.

ShankerV

Hi @kishorhk

The below formula will help to add +1 month to Rundate and then use the date for calculating the datetimediff.

DateTimeDiff([Creation date], (DateTimeAdd([Rundate],1,"months")),"months") <=6

Many thanks

Shanker V

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703