How to count duplicate by categories ?

[ClementByr]

Hello,

I trying to count duplicate by categories in my data, here is an example :

Incident	Machine	NumSerie
A	1	1
A	1	2
B	1	1
A	1	1
C	2	1
D	2	1
B	2	1
D	2	2
F	2	3
D	2	2
D	3	1
D	3	2
G	3	3
G	3	3
A	4	1
G	4	2

In this data, I want to count duplicates per machine. The expected result is suposed to be :

Incident	Machine	NumSerie	Duplicates
A	1	1	3
A	1	2	3
B	1	1	1
A	1	1	3
C	2	1	1
D	2	1	3
B	2	1	1
D	2	2	3
F	2	3	1
D	2	2	3
D	3	1	2
D	3	2	2
G	3	3	2
G	3	3	2
A	4	1	1
G	4	2	1

Can someone help me achieve this results, please ?

Best regards.

[Frank_Hartmann · Accepted solution]

try this expression:

aggr(nodistinct count(Machine),Incident,Machine)

[ClementByr]

Franck,

Thank you for the quick reply.

I tried your formula but it did not work. I am supposed to get the value 2 for the first two A, because there two A Incident duplicate on machine 1.

Best regards.

Edit : After rechecking the results, the formula is the good one. But in the table, Qlik Sense have erased the duplicated rows that share the same value for Incident, Machine and NumSerie.

[Frank_Hartmann]

Yes, I know.

that is why i added rowno()  as Row to my test script and used Row as a further dimension!