3 Replies Latest reply: Feb 25, 2014 9:34 AM by Fabrice Aunez

# Top 10 % of Sales, top n percent

Hey Guys,

I need some help with getting top 10% of Sales to

CustomerSales
A3500
B400
C246
D98
E356
F757
G1300
H5900
I2545
J123
K321

I need to show the list of names in Textbox of top 10% sales (Rank will give me top ten rows, I;m looking for a way to get top 10%)

 Sales %Sales Accmulate %Accumulate H 5900 37.95% 5900 37.95% A 3500 22.51% 9400 60.47% I 2545 16.37% 11945 76.84% G 1300 8.36% 13245 85.20% F 757 4.87% 14002 90.07% B 400 2.57% 14402 92.64% E 356 2.29% 14758 94.93% K 321 2.06% 15079 97.00% C 246 1.58% 15325 98.58% J 123 0.79% 15448 99.37% D 98 0.63% 15546 100.00% 15546 100%

So in this case if I am looking for top 10% then I should get the first row which is customer H

If i am looking for 50%, then I should get Customer H and A

I need to get the list of names in textbox  as i will need to do further search string.

Krishna

• ###### Re: Top 10 % of Sales, top n percent

Krishna,

use the fractile() and concat() function.

Sth like:

=concat({<ID= { "=(Customer > fractile(total Sales, 0.9))"} >} ID, ';')

Because we create a boolean expression in the set analysis, you need a 3d field (here id).

0.9 to get the top 10%, 0.8 to get the top 20% etc.

Fabrice

• ###### Re: Top 10 % of Sales, top n percent

Hi Aunez,

I tried but it still not working.

Let me explain my problem a little more.

 Customer Sales Sales % A 1233 1.10% B 2323 1.20% C 4545 2.30% D 2323 1.32% E 3434 1.90% F 656 4%

the calculation process must first sort by top sales

 Customer Sales Sales% Accumulate Sales % C 4545 2.30% 2.30% E 3434 1.90% 4.20% <-- D 2587 1.32% 5.52% B 2323 1.20% 6.72% A 1233 1.10% 7.82% F 656 0.40% 8.22%

how can i get concat names of those customers within 5% of total sales.

• ###### Re: Top 10 % of Sales, top n percent

Krishna,

1) You may need read a doc I have written on LOAD:

http://community.qlik.com/docs/DOC-5698

Chapter 3.4 will explain you how to sort and do a cumul of the sales.

2) If you want to concat names, sth like the expression below could do the job (it will show you the names of the customers separated by a comma):

=concat({<Customer= { "=([% Sales] < 0.05))"} >} Customer, ';')

Fabrice