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]

you have to do this in script:

load * from tablename order by Amount desc;

by this you get all the records sorted according to amount in descending order.

after that take a variable:

set x = peek('Amount',1,tablename);

you get the second highest value of amount in x.

now again load the table:

a:

load * resident tablename where Amount<= $(x);

by doing this you load only top two records in table a.

try this it will help.

[#3]

Hi,

Thanks for the reply.

We are taking the top 2 values based on the sum(amount) which means for ID 3 the sum is 110 which is the first top and then ID 1 which is 60. We are taking the top2 based on individual amounts. Also we cannot do any script level changes. As we are taking some of the inputs from the user.

Please advise.

[#4]

Hi,

Could you please describe how the output should look like?

Are you looking for the second highest IDs (aggregated sum(Amount) for the countrys) or are you looking for the aggregated values of each ID with the second highest countrys?

[#5]

I am looking for Top 2 ID's (for example, Top2 companies) based on Amount (aggregated by ID). Output should be

ID, Country, Amount

3,'India',60

3,'USA',50

1,'Europe',30

1,'India', 20

1,'UK',10

[#6]

you can use:

a:

load

sum(Amount) as Amt,

ID resident tablename group by ID;

you get the sum of amounts based on ID's

then apply:

load * resident a order by Amt desc;

you get the data in descending order.

try it.

[#7]

I cannot do any calculations at script level as i am taking inputs from the users using Input boxes. So i need to do on the presentation level itself.

We can use RANK(SUM(Amount)). But it is displaying Ranks as below. Rank resets for every ID.

ID, Country, Amount, Rank(sum(Amount))

3,'India',60,1

3,'USA',50,2

1,'Europe',30,1

1,'India', 20,2

1,'UK',10,3

If we get the same Rank for each ID as below then we can solve it.

ID, Country, Amount, Rank(sum(Amount))

3,'India',60,1

3,'USA',50,1

1,'Europe',30,2

1,'India', 20,2

1,'UK',10,2

Please look from this view.

[#8]

if you want to do this at presentation level than simply:

1.take a pivot table ,

2.add dimensions:

ID and country

3.expression:

sum(Amount)

and

4.tick the checkbox of " show partial sum " in the presentation tab after selecting ID in "dimensions and expressions" and sort it in descending order according to sum(Amount).

regards

vijit

[swuehl]

You could try your rank expression like this:

=aggr(NODISTINCT if(rank(sum(total<ID> Amount))<=2,rank(sum(total<ID> Amount)),0),ID)

[miikkaqlick]

Hi!

I'm not sure what You are trying to achieve.

If that is what you want, then:

Expression 1: Sum(Aggr(Sum(Amount),ID))

Expression 2: Rank(Expr1)

Expr3: If(Exp2 = 1 OR Exp2 = 2,Exp2)

BR,

Miikka