Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I had a issue in displaying Top 2 values. I had a table with ID, Country, Amount. I would like to take Top 2 ID's based on Amount.
Load * inline
[ID, Country, Amount
1,'India', 20
2,'UK',40
1,'UK',10
1,'Europe',30
3,'USA',50
3,'India',60
4,'UK',10
5,'India',0
];
In a straight table, I have ID, Country as dimensions and sum(Amount) as expression. I have sorted ID field based on Expression sum(Amount).
Output:
ID, Country, Amount
3,'India',60
3,'USA',50
1,'Europe',30
1,'India', 20
1,'UK',10
Please advise.
Thanks & Regards,
Devaki
Hi swuehl,
I got the rank as expected with your query.
ID | Country | SUM(Amount) | Rank |
220 | - | ||
3 | India | 60 | 1 |
3 | USA | 50 | 1 |
1 | Europe | 30 | 2 |
1 | India | 20 | 2 |
1 | UK | 10 | 2 |
2 | UK | 40 | 0 |
4 | UK | 10 | 0 |
But could you let me know how to restrict the number of rows of table to rank<=2.This might be easy but i did not get the idea how to restrict it.
Hi Miikka,
I want to get Top2 IDs(for example, Companies) which are performing well based on sum(Amount).
Maybe try
if(column(2)>0, sum(Amount))
instead of your sum(Amount) expression. And check suppress zero values in presentation tab.
Suppress Zero values is not working. Also I cannot suppress Zero values as i have some IDs with Zero Amount.
You might try this as caclulated dimension instead of ID
=aggr(NODISTINCT if(rank(sum(total<ID> Amount))<=2,ID),ID)
check suppress when value is NULL on dimension tab.
EDIT: As calculated dimension, it should be possible to simplify above to
=aggr(if(rank(sum(Amount))<=2,ID),ID)
It is working. You are a Genius.
Thank you.
Hi,Swuehl Exlent,Thx