topic Re: 80/20 graph in QlikView

80/20 graph

Sat, 01 Sep 2012 20:30:23 GMT

Dear all,

I've to list the first customers which represent the 80% of the total turnover.

I'm working with the Qlikview 10 Personal Edition.

Table1

Customer Turnover

A 4000

B 5000

C 3000

D 1000

E 1000

F 1500

G 500

In this case: totale turnover 16000 euro

80% = 12800 euro

So I've to list the first four customers.

Does anyone have the solution ? I'm really confused.

Thanks for your help

Stefan

Re: 80/20 graph

Sat, 01 Sep 2012 21:21:05 GMT

In Qlikview 11 there is a possibility to do "pareto select". I have no idea if this also exists in QV10, so you could check that.

What it does is select those customers that contribute x percent (eg 80%) to your turnover (or some other field you define.

Steps to create this

-1: add new sheet object > button

- 2: in its properties, tabpage actions, choose 'pareto select'

- 3: as field place Customers, expression sum(Turnover) and percentage 80 (in all fields: don't put an = sign before it)

- 4: click OK and give the button a name on tab general first

- 5 hit the button. Note that it selects customers a, b, c, f in this case, which means 13.500 is the sum of the turnover because those customers (raked from high to low Turnover) were responsible for the 80% of the turnover.

Hope this helps,.

I made the QVW to check this functionality for myself. I just uploaded it for anyone who is interested. I can imagine you cannot open it (since you use personal edition).

Hope it helps (and I hope it is available too in QV 10)

PS: I see you marked other customers in bold... The Qlikview-pareto-select takes those customers that contributed 80% of the turnover in this case sorted by Turnover descending. Hence the selection of customers customers a, b, c and f

Re: 80/20 graph

Anonymous — Sun, 02 Sep 2012 08:11:28 GMT

Hi, in QV10 this is also possible. In QV11 there is a new and very easy way to achieve this. Create a table (Pivot) and then you go to the Tab Dimension Limits (new in 11). Select "Show only values that accumulate to: 80% relative to the total; using largest values. Also check Include Boundary Values (i.e. F to be included). Very handy, maybe another reason to switch to 11. Unless you have to stay on 10 for some reason. Regards, Sander

Re: 80/20 graph

sebastiandperei — Sun, 02 Sep 2012 16:28:50 GMT

If you don't want to use this QV funciontality, you must:

Dimention: Customer

Sort: Expression sum(Turnover) desc

Expression:

If(rangesum(above(Sum(Turnover),0,rowno()))/sum(total Turnover)<0.8, Sum(Turnover))

0.8 is the limit. If you want to show 60/40, change it by 0.6.

Re: 80/20 graph

ali_hijazi — Mon, 09 Sep 2013 08:40:38 GMT

is there a syntax to do it in the expression definition?