topic Re: Creat Table Chart with Top 3 in expression in QlikView

Creat Table Chart with Top 3 in expression

Anonymous — Tue, 25 Oct 2016 20:23:58 GMT

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!

Re: Creat Table Chart with Top 3 in expression

sunny_talwar — Tue, 25 Oct 2016 21:21:07 GMT

Try with an expression like this:

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

Re: Creat Table Chart with Top 3 in expression

sunny_talwar — Tue, 25 Oct 2016 21:22:26 GMT

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

Re: Creat Table Chart with Top 3 in expression

Anonymous — Wed, 26 Oct 2016 20:58:46 GMT

Thanks Sunny T, this seems to be working!!

Re: Creat Table Chart with Top 3 in expression

Sun, 30 Oct 2016 19:10:04 GMT

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.

Re: Creat Table Chart with Top 3 in expression

sunny_talwar — Mon, 31 Oct 2016 20:47:40 GMT

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?