topic Issue in displaying top2 values in QlikView

Issue in displaying top2 values

Mon, 07 Nov 2011 09:14:45 GMT

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

Issue in displaying top2 values

Mon, 07 Nov 2011 09:47:46 GMT

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:

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

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

try this it will help.

Issue in displaying top2 values

Mon, 07 Nov 2011 09:56:25 GMT

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.

Re: Issue in displaying top2 values

Mon, 07 Nov 2011 10:11:17 GMT

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?

Issue in displaying top2 values

Mon, 07 Nov 2011 10:32:30 GMT

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

Issue in displaying top2 values

Mon, 07 Nov 2011 10:50:32 GMT

you can use:

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.

Issue in displaying top2 values

Mon, 07 Nov 2011 11:05:52 GMT

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.

Issue in displaying top2 values

Mon, 07 Nov 2011 11:23:30 GMT

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

Issue in displaying top2 values

swuehl — Mon, 07 Nov 2011 11:38:36 GMT

You could try your rank expression like this:

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

Issue in displaying top2 values

miikkaqlick — Mon, 07 Nov 2011 11:58:33 GMT

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

Issue in displaying top2 values

Mon, 07 Nov 2011 13:03:18 GMT

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

Issue in displaying top2 values

swuehl — Mon, 07 Nov 2011 13:10:37 GMT

Maybe try

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

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

Issue in displaying top2 values

Mon, 07 Nov 2011 13:18:22 GMT

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

Re: Issue in displaying top2 values

swuehl — Mon, 07 Nov 2011 13:28:41 GMT

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)

Issue in displaying top2 values

Mon, 07 Nov 2011 14:26:40 GMT

It is working. You are a Genius.

Thank you.

Issue in displaying top2 values

Mon, 07 Nov 2011 14:36:38 GMT

Hi,Swuehl Exlent,Thx