Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

Check this if it can be of any help -

Capture1.JPG

Capture2.JPG

View solution in original post

11 Replies
Anonymous
Not applicable
Author

Hi Stewart,

Try this on Straight table:

Regards!

sujeetsingh
Master III
Master III

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
Author

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

Mark_Little
Luminary
Luminary

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
Master III
Master III

Moved to "New to Qlik Sense".

May you live in interesting times!
Digvijay_Singh

Check this if it can be of any help -

Capture1.JPG

Capture2.JPG

reddy-s
Master II
Master II

Hi Stewart,

This document will help you how to achieve this:

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

Not applicable
Author

Wow, the formula looks quite complicated.

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

swuehl
MVP
MVP

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.