Hello everyone, I'm very new to Qlik Sense, and I'd really appreciate help on a query that I have.
I have these transactions data:
Product | Supplier | Spend |
Product A | Supplier 1 | 2000 |
Product A | Supplier 2 | 1200 |
Product A | Supplier 3 | 700 |
Product A | Supplier 4 | 500 |
Product A | Supplier 5 | 500 |
Product A | Supplier 6 | 400 |
Product A | Supplier 7 | 350 |
Product A | Supplier 8 | 325 |
Product A | Supplier 9 | 250 |
Product A | Supplier 10 | 200 |
Product B | Supplier 1 | 2000 |
Product B | Supplier 2 | 1500 |
Product B | Supplier 3 | 750 |
Product B | Supplier 1 | 700 |
Product B | Supplier 2 | 700 |
Product B | Supplier 3 | 400 |
Product B | Supplier 4 | 650 |
Product B | Supplier 5 | 200 |
Product B | Supplier 6 | 100 |
Product C | Supplier 1 | 250 |
Product C | Supplier 2 | 250 |
Product C | Supplier 3 | 200 |
Product C | Supplier 4 | 200 |
Product C | Supplier 5 | 150 |
I would like to create this following report in Qlik Sense (either using pivot table or table). I can't seem to get the last column, which is the count (distinct) of Suppliers contributing up to 80% of the spend for each Product.
Product | Spend | # of Suppliers (Distinct) | 80% Spend | # of Suppliers (Distinct - 80% Spend) |
Product A | 6425 | 10 | 5140 | 5 |
Product B | 7000 | 6 | 5640 | 3 |
Product C | 1050 | 5 | 840 | 3 |
Could anyone please help advise me on what expressions should i use to get that last column? Many thanks in advance!