Let's say that I have 2 tables. The first one represents all my sales, with the order id, product id and a primary key wich combines these 2 fields like this:
Order/Product.ID
Order
Product.ID
1010/0001
1010
0001
1010/0003
1010
0003
1010/0004
1010
0004
1020/0001
1020
0001
1020/0003
1020
0003
1030/0001
1030
0001
1030/0002
1030
0002
1030/0004
1030
0004
1040/0001
1040
0001
1040/0002
1040
0002
The second table, represents the customers' claims, with the exact same fields, but with the defect.id too, like this:
Order/Product.ID
Order
Product.ID
Defect.Id
1010/0001
1010
0001
A
1010/0003
1010
0003
B
1030/0002
1030
0002
B
1040/0001
1040
0001
A
They are linked through Order/Product.ID
Now here is my problem: When I filter for a certain Defect.Id in Qlik Sense I would like to see a KPI which shows how many times the products with this defect were bought.
For example:
If I filter for Defect.Id "A" it should return the value 4 (Just Product 0001 has this defect and it was sold 4 times)
If I filter for Defect.Id "B" it should return the value 4 too ( Product 0002, sold 2 times and Product 0003, sold 2 times)
