Count of products that generate 50% of sales as a KPI in Qlik Sense

[#1]

Hi,

In Qlik Sense, would you know of a way to generate the number of distinct products that make up 50% of all sales? I do not want to put this in a table; rather I would like to display it as a KPI value. I would also like to include in the KPI the percentage of products that make up this 50%. Actually, the ultimate KPI would show something like:

50% of sales are driven by 240 (8%) of our products.

In this case, there are 3000 distinct products of which 240 (8%) make up 50% of sales.

Thank you for your help!!!

Mark

[Anonymous]

To get the count of products (240), something like this should work, maybe with some adjustments:

count({<Product={"=sum(Sales) > $(=fractile(aggr(sum(Sales),Product),0.5))"}>} distinct Product)

When you make it to work, it will be easy to take care of the rest.

[#3]

Hi Michael,

Thank you for your kind reply. I tried your approach and unfortunately, the formula returns the number of products multiplied by (1-fractile) only. In other words, if you use 0.5 as your fractile and there are 3000 products, it returns 1500 without any regard for the Sales amounts. If I am missing something, please let me know.

I am surprised there is scant info on this out there at least for Qlik Sense. Michael or another Qlik expert, if you have other ideas, please share.

Thank you!!!

Mark

[Anonymous]

Sorry I got it wrong...  Here is what can help you:

Recipe for a Pareto Analysis – Revisited

[#5]

Hi Michael,

Thank you for pointing me to this page. I have used a similar method for the actual Pareto chart in my app but needed a way to isolate the 50%. I asked a colleague and once he told me his simple approach, I combined it with the Pareto idea and voila, it works. Here is the approach I now use for getting the number of products that generate 50% of sales. The key is to sum up the number of products by using the 1,0 values below. Then to get the actual percentage, you divide by the distinct number of products. Below is the formula to get the percentage. To get an actual count of products, just remove the num(, the division sign, and line below it. The approach below is a little different and I am sure sloppier than Henric's recipe, but it works. Being that I am new to this advanced analysis, if there are suggestions to clean it up, please share.

num(sum(Aggr(If(

  rangesum(above((Sum({<Product>} Sales)/

  Sum(TOTAL {<Product>} Sales)),0,  RowNo(Total)))<0.5,1,0),

  (Product, (=Sum({<Product>} Sales), DESC))))

  /

  Count(DISTINCT Product),'0%')