Alteryx can't seem to find a date between a range

[WishIKnewHowToCode]

I need to create an alteryx workflow that can identify dates that lay within a date range i give it.

Input 1 will be all of the dates I have and the corresponding account numbers that go with them. There can be multiple dates on a single account number.

Input 2 will be a static date and corresponding account number, there will be no duplicates here.

I need to find dates from Input 1 that lay between -7 and 120 days from Input 2

When I do this, I do a formula tool 

DateTimeDiff([Input 1], [Input 2], "days")

and then a filter

[DateDifference] >= -7 AND [DateDifference] <= 120


Alteryx never returns the right dates. It gives me ones that lay outside of the specified range.

[KGT]

I'm not sure without seeing an example as to why yours is not working. I assume it's something small, but just can't see it. Take a look at the diffs it returns and try figure out how that is being calculated.

If Input 2 is unique on account number, then I would create 2 extra fields on that for the start and end, then join that to Input 1 and apply a filter for >Start and <End.

You could also join Input 2 on Account number, then use a filter with the following:

[Input2Date] >= DateTimeDiff([Input1Date],-7,'day') AND [Input2Date] <= DateTimeDiff([Input1Date],120,'day') 

[apathetichell]

Can you provide some examples? This sounds like you are doing something wrong which can be fixed pretty easily - but it's hard without data/examples.

[WishIKnewHowToCode]

Here's the problem, this is technically patient data. So, I have to create dummy values. I dummied one input and copied the numbers many times over on the other. Hopefully, it can still "solve" with the dummy data. One very annoying aspect of the type of work I do.

[WishIKnewHowToCode]

I posted a workflow and some context in response to the other person.

[Qiu]

@WishIKnewHowToCode 
You can not really blame Alteryx, since your "medicare RA Date" is not in Date format.

[apathetichell]

My hunch is that you are converting your dates incorrectly somewhere -> @Qiu or @KGT will point you in the right direction but the first rule of using datetime is ---- make sure you are actually comparing dates. In your case I think something converted wrong. 

[WishIKnewHowToCode]

Sorry, passed out last night.

Your workflow still produces a result in error. I used the real data and got a result that is -50 days away from the DOFB input. I remember running into this issue last time I did this months ago (ended up giving up back then). I remember trying to tell alteryx that it needed to be 7 and -120 instead of -7 and 120, but the results were still wrong.

[apathetichell]

@WishIKnewHowToCode -> my hunch -> your dates don't all fit the same schema. They are sourced from different upstream sources and some show up as dd/mm/yyyy some might be mm/dd/yyyyy - and some might be yyyy-mm-dd. you have a data problem - not an Alteryx problem. Identify and fix and your workflow will be fine.

[EKasminsky]

A good way to check if your dates are not formatted right is put a select tool next to them, change the data type to "Date" and then see if there are errors. You could filter dates with "/" into one group, convert them using a formula tool, then union the data with any dates that are in the format Alteryx likes the most "yyyy-mm-dd"