Look 2 months and earlier, across different years

[jbfries]

Hi all, 

I have a list of payroll records that includes indications of when Retroactive payments occurred by date. I need to identify any records where the Retro Date is greater than or equal to 2 months prior to the Payment date (Pay Result Payment Date field).  

I have attached my current workflow and removed any confidential data for review. Below I have detailed my current unsuccessful steps. Can anyone help me to update my workflow? 

I was able to use DateTimeFormat(DateTimeAdd) to backdate the Payment date field 2 months and then find the difference b/w the Retro Month and Payment Month (any difference <= 0). Which works in some cases, but for other records it erroneously includes records.

For instance below is a February 2021 record, I would want to identify any Retro Payments that occurred in December 2020 and earlier. So my formula correctly retains the below December 2020 retro payment, but also retains the Jan 2021 payment b/c 1-12 = -11 which is less than 0 , per my formula. Same error occurs for January 2021 records. And really could occur for any month depending on the Pay month vs. Retro Month. (i.e. if March payment and retro month of December 12-3=9 and my current formula would incorrectly exclude.)

I somehow need a formula that will simultaneously consider the month and year where identifying applicable records to retain. 

Thank you!

[OllieClarke]

Hey @jbfries I think a better approach here is to use the datetimediff() function to count the months between the Pay Result Payment Date and the Retro Date. 

So you can filter using
DateTimeDiff([Pay Result Payment Date],[Retro Date],'month')>=2
To just find the records whose Retro Date is more than 2 months before its Pay Result Payment Date.

[jbfries]

Hi Ollie, 

Thank you for your help!

The one thing I'm seeing is that the filter is excluding 40 December 2020 records. (example below)

I'm guessing because Alteryx bases 2 months as 60 days, and so these Retro pay records occurred less than '60' days ago from the Payment Date field. 

How could we prevent this from happening? So for February, any Retro dates occurring in December 2020 (and earlier) are captured. That is where the change in years had me tripped up. 

(For January, any Retro dates occurring in November 2020 (and earlier) are captured, etc.) 

Thanks!

[binuacs]

@jbfries I calculated the difference from the first of the month of PayResultPaymentDate and Retro Date. Also optimized your workflow . please have a look and advise ?