6 Replies Latest reply: Jul 19, 2018 3:02 AM by Angelos Giazitzoglou

# Count based in ranking

I have a data base with sales per customer and I want to built a bar chart where the first bar shall have the sales for the top three customers, the second one the sales for the top five etc. Can anyone help me with this?

My base looks like this

 Customer Sales Qnt Cust. 1 100 Cust. 1 10 Cust. 1 20 Cust. 2 35 Cust. 2 68 Cust. 3 14 Cust. 3 33 Cust. 3 69 Cust. 4 78 Cust. 4 40 Cust. 4 74 Cust. 4 54 Cust. 4 87 Cust. 5 56 Cust. 5 87 Cust. 5 23 Cust. 5 10 Cust. 5 5 Cust. 6 14 Cust. 6 32 Cust. 6 98 Cust. 6 100 Cust. 6 55 Cust. 6 21

I am new with Qlik sense.

Thank you in advance,
Angelos

• ###### Re: Count based in ranking

Try like this

Script

LOAD * INLINE [

Customer, Sales Qnt

Cust. 1, 100

Cust. 1, 10

Cust. 1, 20

Cust. 2, 35

Cust. 2, 68

Cust. 3, 14

Cust. 3, 33

Cust. 3, 69

Cust. 4, 78

Cust. 4, 40

Cust. 4, 74

Cust. 4, 54

Cust. 4, 87

Cust. 5, 56

Cust. 5, 87

Cust. 5, 23

Cust. 5, 10

Cust. 5, 5

Cust. 6, 14

Cust. 6, 32

Cust. 6, 98

Cust. 6, 100

Cust. 6, 55

Cust. 6, 21

];

Dim:

LOAD * INLINE [

Dim

Top 3

Top 5

];

On the front end....

Dimension

Dim

Expression

Pick(Match(Dim, 'Top 3', 'Top 5'),

Sum({<Customer = {"=Rank(Sum([Sales Qnt])) < 4"}>}[Sales Qnt]),

Sum({<Customer = {"=Rank(Sum([Sales Qnt])) < 6"}>}[Sales Qnt]))

• ###### Re: Count based in ranking

Thank you very much for your response.

Is there any way to avoid the use of script because my data base is not so small and simple as the example

• ###### Re: Count based in ranking

May be use ValueList() function to create a synthetic dimension on the front end

• ###### Re: Count based in ranking

Basically, is it possible instead of script to have an external excel file from which I will load data any time I choose

• ###### Re: Count based in ranking

Can you elaborate on this? May be with an example

• ###### Re: Count based in ranking

It is pure magic

Thank you so very much