11 Replies Latest reply: Jul 1, 2016 4:50 AM by Digvijay Singh

# Top Suppliers for 80% Spend

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

• ###### Re: Top Suppliers for 80% Spend

Hi Stewart,

Try this on Straight table:

Regards!

• ###### Re: Top Suppliers for 80% Spend

There are number of ways t o do that:

* Use dimension limits they are too fast and worth

* Use calculated dimension this is time consuming one.

• ###### Re: Top Suppliers for 80% Spend

Sorry, I posted this question on a wrong forum. I'm actually using Qlik Sense (not Qlik View).

• ###### Re: Top Suppliers for 80% Spend

Moved to "New to Qlik Sense".

• ###### Re: Top Suppliers for 80% Spend

HI,

I am not sure how you work out the last column i..e the logic that is used.

Other than that you can use a use a straight table from charts.

Dimension would be Product.

Expressions

Spend = Sum(Spend)

# of suppliers = Count(DISTINCT Supplier)

80% of spend = SUM(Spend)*0.80

Mark

• ###### Re: Top Suppliers for 80% Spend

Check this if it can be of any help -

• ###### Re: Top Suppliers for 80% Spend

Wow, the formula looks quite complicated.

This is exactly what I needed. It works flawlessly. Thanks so much!

• ###### Re: Top Suppliers for 80% Spend

I think this only works if the LOAD order of supplier is already listing the suppliers by Spend descending for every Product, like in your sample data, but I doubt that your real data will show the same property.

• ###### Re: Top Suppliers for 80% Spend

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).

• ###### Re: Top Suppliers for 80% Spend

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

• ###### Re: Top Suppliers for 80% Spend

Hi Stewart,