Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Set analysis query

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

1 Solution

Accepted Solutions
Employee
Employee

Re: Set analysis query

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

expre attr.png

12 Replies
Employee
Employee

Re: Set analysis query

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

max(total <country> highest)

HIC

Not applicable

Re: Set analysis query

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

Not applicable

Re: Set analysis query

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

Employee
Employee

Re: Set analysis query

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

max({1} total <country> highest)

HIC

Not applicable

Re: Set analysis query

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.

Employee
Employee

Re: Set analysis query

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

Not applicable

Re: Set analysis query

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.

Employee
Employee

Re: Set analysis query

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

Not applicable

Re: Set analysis query

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.

Community Browser