topic Re: Max of both values in QlikView

Max of both values

Fab62400 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I've a table like this :

I have a table like this. I would like to find the max of "Amount" and also the corresponding "Team" data.
For example :
for the date of 01/08/2013:
the max of Amount is 25000 and the Team is "Alpha".
I know how to find the maximum of Amount but not the Team column with set analisys.

=max ({<[SalesGroup]= {'East'}>} VALUE,1)

I would like to display both values.

Thank for your help !

Re: Max of both values

martinpohl — Fri, 29 May 2020 09:55:46 GMT

you need something like

MaxString( if( aggr(nodistinct max(Amount), Date) = Amount, Team, null()))

Regards

Re: Max of both values

tresB — Fri, 29 May 2020 10:05:29 GMT

May be a simpler one for Team value like:

=FirstSortedValue ({<[SalesGroup]= {'East'}>} Team, -Amount)

Re: Max of both values

Fab62400 — Fri, 29 May 2020 13:17:49 GMT

Thanks it works!

Is it possible to have the 3 max text value like the function max(.., 1) max(.., 2) max(.., 3 ) ?

Re: Max of both values

sunny_talwar — Fri, 29 May 2020 13:39:45 GMT

Just like Max, FirstSortedValue can also show text related to 2nd and 3rd max value

=FirstSortedValue ({<[SalesGroup]= {'East'}>} Team, -Amount, 1) =FirstSortedValue ({<[SalesGroup]= {'East'}>} Team, -Amount, 2) =FirstSortedValue ({<[SalesGroup]= {'East'}>} Team, -Amount, 3)

Re: Max of both values

Fab62400 — Fri, 29 May 2020 13:44:54 GMT

Yess !

Thank you !