Filtering Date data

[Adam_B]

Hello, 

I have a date data set that has data from 2012 through to today.  So I now there is a Today() function in there somewhere. 

I would like to filter the data in Alteyx before exporting it to my analytics tool. The only way I could think to do this is by a formula but none I am using seem to be working.

Any help would be appreciated. 

Thanks, 

Adam

[Luke_C]

Hi @Adam_B 

Are you just working with years or do you have actual dates? There is a datetimetoday() function that can be used to filter date data, but based on your screenshot it seems to just be years. There are a few different ways to accomplish this - I attached an example where I generated a record for every day from 2011 through 2022, and then filtered it to just 2012 through today using the datetimetoday function - hopefully this gets you in the right direction. 

[Adam_B]

Hi Luke, 

I have a PERIOD data field that has a date format as "YYYY-MM-01" the other field I have is YEAR, that is only "YYYY".

Thanks, 

Adam 

[Adam_B]

So if I wanted date from 2018 though today would the formula look like this? 

[DATE] >= '2012-01-01' and [Date] <= '2017-01-01'

Thanks, 

Adam 

[Luke_C]

Hi @Adam_B 

The filter tool has some built in date options for filtering that you can leverage once you have a date field formatted properly. A filter for Jan 2018 through today might look like the below screenshot. If you want 'today' to be dynamic, click into the custom filter and update the second date clause to be datetimetoday(). Let me know if this answers your question, otherwise is there some sample data you can share in excel? Screenshots are a bit tough to work with. 

[Date] >= "2018-01-01" AND
[Date] <= DateTimeToday()

[Adam_B]

When entering in the formula referenced, I get this error:

[Luke_C]

Hi @Adam_B 

This is happening because you're using a formula tool. The error here is because you haven't named the column (you can do so by clicking 'select column' in the top left). Using this formula in the formula tool will result in a column that gives you a boolean result that you would then need to use a filter tool for. The more direct way is to use a filter tool and enter the expression in the custom box (seen in above screenshots).