Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be use ValueList() function to create a synthetic dimension on the front end
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]))
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
Basically, is it possible instead of script to have an external excel file from which I will load data any time I choose
May be use ValueList() function to create a synthetic dimension on the front end
Can you elaborate on this? May be with an example
It is pure magic
Thank you so very much
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