Creat Table Chart with Top 3 in expression

[Anonymous]

Hello there,

I was trying to create a table chart and I don't know if it's possible.

Let's suppose I have the following data:

I want a chart where I can watch in a column the Sum Sales (No problem on this).
In other column some kind of top 3, showing which Subgroup have higher sales.

For example:

I try to use the rank function but without much success, anybody can send me a clue??

Thanks!

[sunny_talwar · Accepted solution]

Try with an expression like this:

Concat(Aggr(If(Rank(Sum(Sales), 4, 1) <= 3, Subgroup), Group, Subgroup), ' - ')

[sunny_talwar]

Or this if you want to sort in the order of their rank instead of alphabetical order:

Concat(Aggr(If(Rank(Sum(Sales), 4, 1) <= 3, Subgroup), Group, Subgroup), ' - ', Aggr(Rank(Sum(Sales), 4, 1), Group, Subgroup))

[Anonymous]

Thanks Sunny T, this seems to be working!!

[#5]

Hi Sunny T,

I am new to QV so please pardon my silly follow up question, I am going through the below expression,

Concat(Aggr(If(Rank(Sum(Sales), 4, 1) <= 3, Subgroup), Group, Subgroup), ' - ')

Wherein i am not able understand how the value is populated, If i break it and run it till the last step its just a '-' but as soon as concat is added all the code falls in place.

Could you please help me understand the above expression.

[sunny_talwar]

Concat is an aggregate function which combines multiple values into one row (just like Sum adds up multiple values to show the total of all those rows). So if you use it without Concat, how would QlikView know what you are trying to do? Are you looking to sum them, avg them, MinString them, MaxString them (Remember there is more than one value). But as soon as you add Concat, qlikview know that you want to show them next to each other with ' - ' separating each of the rows value.

Does the above make sense?