How to display name with highest score

[#1]

I want to make a table which displays the name with the highest score of each exercise. The data is loaded from an excel spreadsheet like this:

exercise	name	points
exercise1	nameA	10
exercise1	nameB	23
exercise1	nameC	40
exercise2	nameA	16
exercise2	nameB	12
exercise2	nameC	6
exercise3	nameA	4
exercise3	nameB	8
exercise3	nameC	5

This is how I want the result table to look like:

Exercise	winner	points
exercise1	nameC	40
exercise2	nameA	16
exercise3	nameB	8

I am using exercise as dimention and max(points) for points, but how do i set up the expression for winner?

[flipside · Accepted solution]

Try

=FirstSortedValue(name,-points)

flipside

[IAMDV]

Hi,

I understand you have the answer you needed. However, if you are keen to learn more about FirstSortedValue() then look at my blog. I have made a series of video tutorials on this topic.

http://qlikshare.com/tag/firstsortedvalue

Happy learning!

Cheers,

DV

[#4]

If you are using a chart, you can also do it with Dimension Limits. You can filter your data with your first expression in the chart. There are many different way to filter in Dimension Limits section on Chart Properties by your first expression.