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

80/20 graph

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

4 Replies
Not applicable
Author

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

Anonymous
Not applicable
Author

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

sebastiandperei
Specialist
Specialist

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.

ali_hijazi
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes