Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Top Suppliers for 80% Spend

Hello everyone,

I have these transactions data.

   

ProductSupplierSpend
Product ASupplier 12000
Product ASupplier 21200
Product ASupplier 3700
Product ASupplier 4500
Product ASupplier 5500
Product ASupplier 6400
Product ASupplier 7350
Product ASupplier 8325
Product ASupplier 9250
Product ASupplier 10200
Product BSupplier 12000
Product BSupplier 21500
Product BSupplier 3750
Product BSupplier 1700
Product BSupplier 2700
Product BSupplier 3400
Product BSupplier 4650
Product BSupplier 5200
Product BSupplier 6100
Product CSupplier 1250
Product CSupplier 2250
Product CSupplier 3200
Product CSupplier 4200
Product CSupplier 5150

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.

     

ProductSpend# of Suppliers (Distinct)80% Spend# of Suppliers (Distinct - 80% Spend)
Product A64251051405
Product B7000656403
Product C105058403

Could anyone please help advise me on what expressions should i use to get that last column? Many thanks in advance!

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: Top Suppliers for 80% Spend

Check this if it can be of any help -

Capture1.JPG

Capture2.JPG

11 Replies
manucamon
Valued Contributor III

Re: Top Suppliers for 80% Spend

Hi Stewart,

Try this on Straight table:

Regards!

sujeetsingh
Honored Contributor III

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.

Not applicable

Re: Top Suppliers for 80% Spend

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

mark6505
Valued Contributor III

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

oknotsen
Honored Contributor III

Re: Top Suppliers for 80% Spend

Moved to "New to Qlik Sense".

May you live in interesting times!
Digvijay_Singh
Honored Contributor III

Re: Top Suppliers for 80% Spend

Check this if it can be of any help -

Capture1.JPG

Capture2.JPG

reddys310
Honored Contributor II

Re: Top Suppliers for 80% Spend

Hi Stewart,

This document will help you how to achieve this:

Qlik Design Blog : Recipe for a Pareto Analysis | Qlik Community

Not applicable

Re: Top Suppliers for 80% Spend

Wow, the formula looks quite complicated.

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

MVP
MVP

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.