topic Re: 20 / 80 Calculation in QlikView

20 / 80 Calculation

Anonymous — Tue, 12 May 2015 08:23:35 GMT

Hi,

I have the need to calculate the following "The percentage of sales that my top 20% (number of) suppliers are responsible for".

A classical 20/80 check which is fairly simple to do in Excel.

My challenge here is that I need this number over time as well, so basically a table like:

Year Month | 20% Supplier Sales | 100% Sales | Top 20% Suppler Share

201504 | 632 | 1000 | 63,2%

201504 | 600 | 1200 | 50%

I am not interested in which the top 20% suppliers are, so no need for the "Pareto"-selection button.

Attached is a dummy application, where I have excluded the complexity of "time", but where the end result would be something like:

Year Month | 20% Supplier Sales | 100% Sales | Top 20% Suppler Share

XXXXX | 750 | 1520 | 49,34%

ANY ideas are appreciated.

Kind regards

Niklas

PS Please, notice that I could achieve the result by using all needed dimensions, but I need to narrow it down to one fantastic aggr/sum/if/setanalyis/percentile-formula, which could be used without showing all suppliers/names etc.

Re: 20 / 80 Calculation

Gysbert_Wassenaar — Tue, 12 May 2015 08:35:07 GMT

Try:

Variable vTopX: =ceil(count(distinct Name)*0.20)

Expression: =num(sum({<Name={"=rank(sum(Amount))<=$(vTopX)"}>}Amount)/sum(Amount),'#.00%')

Re: 20 / 80 Calculation

Anonymous — Tue, 12 May 2015 08:51:30 GMT

Ouch. You are so good. Just excellent!

(I do hope that you had that solution in your back pocket and did not figure it out from scratch in 3 minutes. Took me probably 5 minutes to write down the question...)

Kind regards

Niklas

Re: 20 / 80 Calculation

Anonymous — Tue, 12 May 2015 11:44:08 GMT

Can I make it more challenging and add the time dimension to the problem...

See updated attached file with.

Kind regards

Niklas

Re: 20 / 80 Calculation

Gysbert_Wassenaar — Tue, 12 May 2015 12:40:19 GMT

See attached qvw. Is that what you need?

Re: 20 / 80 Calculation

Anonymous — Tue, 12 May 2015 12:48:37 GMT

Unfortunately not. That is cheating using the Name-dimension. 😉

I can only have Time and Expression.

(We are actually talking about a couple of thousand "Names")

The "Over Time"-table is basically what I need, but with correct calculation(s) (disregard the "Top Names", which is just a test).

From what I have read I am a bit hesitant that set analysis will do the job over several rows, given that that will be an initial selection for the chart. Might have to revert to precalculations in the script.

(Please, do not overspend your valuable time on this problem unless you also find it interesting/challenging.)

Kind regards

Niklas

Re: 20 / 80 Calculation

robert99 — Sun, 07 Jun 2015 10:31:25 GMT

Thanks. Just what i need