Get value in field X where max(field Y)

[#1]

Hello,

i'm just getting started with Qlikview.

I was wondering how to add an expression to a textobject where the value has to come from field x where field y is the highest value.

I have something like this: =if(max(Y), X) but this doesn't work...

Thanks in advance

[stephencredmond]

Hi,

You should be able to use FirstSortedValue for this. Y will need to be numeric.

Stephen

[#3]

You can try set analysis as well:

Fisrt create a variable as vMax=max(Y)

Then, the text object value can be =max({$<Y={$(vMax)}>} X)

[tabletuner · Accepted solution]

Hi

In addition to Stephen's answer you have to be careful using FirstSortedValue if there are multiple records with the same highest Y value. You may want to use distinct as a keyword like:

=

FirstSortedValue(DISTINCT X,-Y)

Also note the minus sign before the Y field.

I assumed the following table:

X	Y
1	5
2	6
3	7
4	8
5	8

[#5]

Thanks a lot! It worked!

René