Issue with filter not capturing certain dates within a range

[oosegoose]

Hello,

I am attempting to run a fairly straightforward filter on a dataset to only include dates between a certain range (4/1/2022 and 6/30/2022). I used the Text to Columns, Select, and Formula tools to do some initial cleanup. The first Filter tool is where I attempted to filter the dataset for just the date range I care about, which resulted in 2024 total records. I gut checked this by running the same filter in Excel and noted that there were 2205 records total. After doing some digging, I found that the records with a 6/9/2022 and 6/6/2022 date were excluded from the date range for some reason. Therefore, I ran additional filters against the False path from the first Filter tool to pull in records with these specific dates (6/9/2022 and 6/6/2022), which resulted in 173 records for 6/9/2022 and 8 records for 6/6/2022. The total records between these three filters is 2205 (2024+173+8), the same as the total that I saw in my Excel gut check file.

Does anyone know what would be causing these 6/9/2022 and 6/6/2022 dates to be excluded from the first Filter that I executed in the workflow?

Thanks!

[DataNath · Accepted solution]

@oosegoose if you change your filter condition to the following, does it work as expected?

DateTimeParse([Date],'%m/%d/%Y') <= '2022-06-30'
AND
DateTimeParse([Date],'%m/%d/%Y') >= '2022-04-01'

Alteryx uses YYYY-MM-DD (+HH:MM:SS if DateTime) as a standard Date format and so currently you're trying to filter using an incorrect format. I believe Alteryx will still just be looking at your 'dates' as strings rather than dates and so won't be behaving as expected. The DateTimeParse() function above just allows you to convert a string into Alteryx's Date format by providing the incoming format (mm/dd/yyyy in your case).

[oosegoose]

Edit: Disregard this note, thanks for the explanation above!

Yes this worked! Can you help explain what caused this issue when using the filter I used initially?