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 ,

Can you copy paste data instead of image ?  

example :

sample	col
a	12
b	13
c	14

[laha10ae]

Hi @benakesh ,
Thanks for your reply. Here you go!

Value	Name	date
100	a	10-10-2019
200	b	11-10-2019
300	c	12-10-2019
101	a	11-10-2019
400	b	14-10-2019
500	c	15-10-2019
600	d	16-10-2019
700	e	17-10-2019
800	f	18-10-2019
900	g	19-10-2019
1000	h	20-10-2019
1100	i	21-10-2019
1200	j	22-10-2019
1300	k	23-10-2019
1400	l	24-10-2019

[benakesh · Accepted solution]

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

[OllieClarke · Accepted solution]

Hi Lasse, here's a workflow that does what you're after. Option 1 results in the same as your Excel worksheet. Option 2 includes a list of the possible duplicates.

[laha10ae]

Hi @OllieClarke and @benakesh

Both solutions do exactly what I need. Thanks a lot for your help!

Regards

Lasse