CountIfs based on dynamic similar values

laha10ae

Hi, hope that anyone can help me with the issue that I have been battling with for some time now.

I'm trying to switch from Excel to Alteryx and I have a COUNTIFS function which does not look for an exact match, but a range of values and dates in order to find "possible" duplicates if data is similar.

Conditions:

Value: If the value is similar to another value up to (+- 2 %) 

Name: If the name is the same

Date: If the payment has been made on the same date or up to + - 1 day.

Example of possible duplicate:

In the below example Row 2 and Row 5 would be a possible duplicate, as the name is the same, values are similar (within +- 2 %) and the payment dates are not more than +- 1 day appart.

Regards

Lasse

benakesh

Hi @laha10ae ,

Let me know if this  works.

Conditions:

Value: If the value is similar to another value up to (+- 2 %)   

[Right_Value] >= ([Value] - [Value] * .02 ) and
[Right_Value] <= ([Value] + [Value] * .02 ) 

Name: If the name is the same     : join  tool  

Date: If the payment has been made on the same date or up to + - 1 day.   

DateTimeDiff([DateTime_Out],[Right_DateTime_Out],'days') < 2

countif.yxmd