Issue in displaying top2 values

[#1]

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

[#2]

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).

[swuehl]

Maybe try

if(column(2)>0, sum(Amount))

instead of your sum(Amount) expression. And check suppress zero values in presentation tab.

[#4]

Suppress Zero values is not working. Also I cannot suppress Zero values as i have some IDs with Zero Amount.

[swuehl · Accepted solution]

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)

[#6]

It is working. You are a Genius.

Thank you.

[#7]

Hi,Swuehl   Exlent,Thx