Solved: Re: Set analysis query - Qlik Community

Report Inappropriate Content · ‎2012-10-07

Hi, In the attached qvw I want the expression in the "Highest score by country" chart to be fixed irrespective of the selection. I know i need to do by set analysis and need to {1}, but not sure how to use that in this expression.

aggr(max({1}highest, country) doesn't seem to do the trick. Kindly help.

Thanks

Ram

hic · ‎2012-10-07

Skip the visual cues. It's an old fashioned way to do this. Instead you can use expression attributes. The test will then be if max(total <country> ...) equals max(total ...).

For color, use e.g.

if(max({1}total <country> highest)=max({1}total highest),red())

For bold and underline (text format), use e.g.

if(max({1}total <country> highest)=max({1}total highest),'<B><U>')

HIC

View solution in original post

hic · ‎2012-10-07

You don't need to use the aggr function for this. Instead you should use

max(total <country> highest)

HIC

Report Inappropriate Content · ‎2012-10-07

Thanks Henric for the response, but the behaviour is still the same even after the change.

Report Inappropriate Content · ‎2012-10-07

Thanks Henric for the response, but the behaviour is still the same even after the change.

hic · ‎2012-10-07

If you want it not to change also when you have a selection, then use

max({1} total <country> highest)

HIC

Report Inappropriate Content · ‎2012-10-07

after that change, it has started giving completely wrong results.

e.g 321 is displayed as the highest score for both the players in India.

hic · ‎2012-10-07

But 321 is the highest score by country irrespective of the selection - same number for both Indian players. If this isn't what you want, then I do not understand what you number you want to display.

HIC

Report Inappropriate Content · ‎2012-10-07

1. What I want is actually the output of aggr(max(highest), country ), This gives me 6 rows and I have 6 distinct countries with the highest score of each country and the player who holds that highest score.

2. The moment I change to max(total<country>highest), this returns me 9 rows, I have 9 players but I don't want this chart to return 9 rows.

All I want is the output of 1 not to change regardless of the selection. Hope I have made myself clear.

Thanks.

hic · ‎2012-10-07

OK, you want one line per country, right? Six lines. Then you should use country as dimension - and no other dimensions.

First expression is "max({1} highest)" which will return the highest score per country. The {1} will evaluate the expression without selections, which I think is what you want.

Second expression is "FirstSortedValue({1} [Player Name],-highest)" which will return the first player name once you sort it according to -highest. The minus sign is to get the highest numbers first.

HIC

Report Inappropriate Content · ‎2012-10-07

Thanks Henric. This works like a dream.

I just have one last problem to sort out. I have a Visual Cue setting for the first expression 'Highest'. My requirement is to highlight the top scorer of the world in a different colour. In my data it is Ricky Ponting from Australia. My Visual Cue Setting is

Upper >= max(highest)

This works fine if there is no selection or selection is 'Australia'. The moment I select any other country the result goes ragged. Kindly help.