Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
is there a syntax to do it in the expression definition?