topic Re: Set analysis query in QlikView

Set analysis query

Sun, 07 Oct 2012 10:02:47 GMT

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

Re: Set analysis query

hic — Sun, 07 Oct 2012 11:48:38 GMT

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

max(total <country> highest)

HIC

Re: Set analysis query

Sun, 07 Oct 2012 11:57:36 GMT

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

Re: Set analysis query

Sun, 07 Oct 2012 11:58:11 GMT

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

Re: Set analysis query

hic — Sun, 07 Oct 2012 12:01:46 GMT

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

max({1} total <country> highest)

HIC

Re: Set analysis query

Sun, 07 Oct 2012 12:04:13 GMT

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.

Re: Set analysis query

hic — Sun, 07 Oct 2012 12:10:04 GMT

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

Re: Set analysis query

Sun, 07 Oct 2012 12:19:12 GMT

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.

Re: Set analysis query

hic — Sun, 07 Oct 2012 12:30:32 GMT

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

Re: Set analysis query

Sun, 07 Oct 2012 12:47:25 GMT

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.

Re: Set analysis query

hic — Sun, 07 Oct 2012 12:59:44 GMT

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

Re: Set analysis query

Sun, 07 Oct 2012 13:24:06 GMT

That is an eye-openeer for me. Very new to qlikview just 2 weeks old, but i learnt a lot of things today, your courtesy. Thanks a lot. Want to learn a lot more. Following you on twitter now.

Re: Set analysis query

hic — Sun, 07 Oct 2012 14:50:52 GMT

Take a look at the blog posts also.

HIC