Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be something like below
=FirstSortedValue(Country, -Aggr(SUM(Amount),Country)) & '-' &FirstSortedValue(Aggr(SUM(Amount),Country), -Aggr(SUM(Amount),Country))
May be something like below
=FirstSortedValue(Country, -Aggr(SUM(Amount),Country)) & '-' &FirstSortedValue(Aggr(SUM(Amount),Country), -Aggr(SUM(Amount),Country))
Thanks Manish.
Working Fine!
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))