Skip to main content
Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for 
Search instead for 
Did you mean: 
giazitzoglou
Contributor III
Contributor III

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

CustomerSales Qnt
Cust. 1100
Cust. 110
Cust. 120
Cust. 235
Cust. 268
Cust. 314
Cust. 333
Cust. 369
Cust. 478
Cust. 440
Cust. 474
Cust. 454
Cust. 487
Cust. 556
Cust. 587
Cust. 523
Cust. 510
Cust. 55
Cust. 614
Cust. 632
Cust. 698
Cust. 6100
Cust. 655
Cust. 621

I am new with Qlik sense.

Thank you in advance,
Angelos

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

7 Replies
sunny_talwar

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]))

Capture.PNG

giazitzoglou
Contributor III
Contributor III
Author

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

giazitzoglou
Contributor III
Contributor III
Author

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

sunny_talwar

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

sunny_talwar

Can you elaborate on this? May be with an example

giazitzoglou
Contributor III
Contributor III
Author

It is pure magic

Thank you so very much

giazitzoglou
Contributor III
Contributor III
Author

I think I need your help once more. I already use the Value list solution that you propose and now I need to narrow the results inserting a criteria in it.

I tried the following but it seems that is not working properly.   

 if(ValueList('Top 1', 'Top 3', 'Top 5','Top 10')='Top 1',Sum({<[Group of Customers] = {"=Rank(Sum([Sales Qnt])) = 1"}, Division={Al}>}[Sales Qnt]), if(ValueList('Top 1', 'Top 3', 'Top 5','Top 10')='Top 3',Sum({<[Group of Customers] = {"=Rank(Sum([Sales Qnt])) < 4"}, Division={Al}>}[Sales Qnt]), if(ValueList('Top 1', 'Top 3', 'Top 5','Top 10')='Top 5',Sum({<[Group of Customers] = {"=Rank(Sum([Sales Qnt])) < 6"}, Division={Al}>}[Sales Qnt]), if(ValueList('Top 1', 'Top 3', 'Top 5','Top 10')='Top 10',Sum({<[Group of Customers] = {"=Rank(Sum([Sales Qnt])) < 11"}, Division={Al}>}[Sales Qnt])))))

 

Thank you in advance,

Angelos