Hi Guys, I'm having trouble with a calculation.
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)
Thanks in advance!
