Show the name for The Biggest Value

[ariel_klien]

Hello all Experts,

I have a table below:

LOAD * INLINE [

    Country, Amount, Date

    Israel, 100, 01/01/2014

    USA, 60, 01/01/2014

    UK, 30, 01/01/2014

    Israel, 20, 02/01/2014

    USA, 100, 02/01/2014

    UK, 90, 02/01/2014

    Israel, 10, 03/01/2014

    USA, 70, 03/01/2014

    UK, 100, 03/01/2014

];

I want to show here the biggest country and his amount for the Selected dates.

for example:

if I select 01/01/2014 - Israel - 100

if I select all of the dates : USA - 230

if I select 02/01/2014 +02/01/20143 : UK - 190

Any Idea?

I attached a file. with this load.

Thanks,

Ariel

[MK_QSL · Accepted solution]

May be something like below

=FirstSortedValue(Country, -Aggr(SUM(Amount),Country)) & '-' &FirstSortedValue(Aggr(SUM(Amount),Country), -Aggr(SUM(Amount),Country))

[ariel_klien]

Thanks Manish.

Working Fine!

[MK_QSL]

In case for any selected date if more than two countries are having same total... you can use below

=FirstSortedValue(DISTINCT Country, -Aggr(SUM(Amount),Country)) & '-' &FirstSortedValue(DISTINCT Aggr(SUM(Amount),Country), -Aggr(SUM(Amount),Country))