Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
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 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!
Seems that you are right on that note. My real data definitely doesn't come already sorted like the sample above. Moreover, I found that if we have 1 big supplier that has >80% of spend, then the count of supplier is 0 (which should be 1 in real case).
The work around to show 1 big supplier is as below -
Aggr(
Count(
if(Aggr(top(Sum(Spend)/Sum(Total<Product>Spend)),Product)>0.8,1,
if(Aggr(Rangesum(Above(Sum(Spend)/Sum(Total<Product>Spend),0,Rowno())),Product,Supplier)<=0.8,Supplier))
)
,Product)
But I suppose the expression will still exclude last supplier above 80% in the situation when 4 suppliers contribute 79% or so and adding one more goes above 80%. The last one won't be counted.
You would need to sort tables spend wise in the script to make it work as swuehl pointed out.(Thanks!)
I tried to use new sortable Aggr function but I think it doesn't allow expression based sorting(Sum(Spend)).
See the attached sample where I changed the script to sort the table spend wise, surprisingly I need to change the Supplier field name to see the 'Order By' impact when doing resident load of old table, swuehl can you pl help to understand this, Is it related to unchanged symbol table order for supplier,not sure