topic Re: Vendor Count in QlikView

Vendor Count

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Am trying to write a formula that will count the number of vendors whose total spend make up 80% of total spend.

The formula I have now for 80% spend is

Sum([Invoice Amount USD])*0.8

Count of Vendors formula:

Count(distinct([Harmonized Supplier Name]))

The vendors are sorted using their spend from largest to lowest.

The essence of this formula am seeking is for me to know how many vendors make up 80% of total spend.

thanks

Re: Vendor Count

sunny_talwar — Thu, 02 Nov 2017 18:50:09 GMT

This might be a good place to start

Recipe for a Pareto Analysis – Revisited

Re: Vendor Count

Anonymous — Thu, 02 Nov 2017 21:03:53 GMT

Is there one for Qlik Sense? I noticed that these are all for Qlik View. Kindly advise

Re: Vendor Count

sunny_talwar — Thu, 02 Nov 2017 22:47:12 GMT

This works for QlikView and Qlik Sense

Re: Vendor Count

Anonymous — Fri, 03 Nov 2017 17:39:09 GMT

Thank you for your response. When I add the formula, I do not have the Desc option as it is not recognized. Additionally although the formula works, I get a bad class name error.

Formula: Background Color expression

If([Pareto Class] = 'A', LightGreen(),If([Pareto Class] = 'C', LightRed(), If([Pareto Class] = 'B', Yellow())))

And Pareto

If([Exclusive Percentage]<= 0.8, 'A', If([Exclusive Percentage] <= 0.9, 'B', 'C'))

I am also trying to create a KPI that Counts the number of Vendors within Pareto Class A but cannot get it to work.

count(distinct({<'[Exclusive Percentage]' <= {0.8}>}[Harmonized Supplier Name]))

What is wrong with this formula?

Re: Vendor Count

sunny_talwar — Fri, 03 Nov 2017 18:19:44 GMT

What exactly are you doing? Can you share a sample to look at?

Re: Vendor Count

Anonymous — Fri, 03 Nov 2017 18:50:27 GMT

I built the Pareto and it works even with the Bad Field Names. I cannot create a KPI to count the number of vendors within Pareto Class A.

Re: Vendor Count

sunny_talwar — Fri, 03 Nov 2017 18:58:32 GMT

Check here:

Count values ordered by expression (Aggr, above, sum)