topic Re: Using Aggr Function Top N in QlikView

Using Aggr Function Top N

muniyandi — Tue, 22 Jul 2014 05:59:29 GMT

I need aggr function clarification . i has displayed Top 5 , Bottom 5 in the calculated dimension.it's based on Amount2 Value

Top 5 = if(Aggr(rank(Sum(Amount2))<=5,Name),Name)

Bottom 5 = if(Aggr(rank(-Sum(Amount2))<=5,Name),Name)

Now i have problem on some cases Below mentioned my demo data. Look Name C Records,

My formula baseon Amount2 . So Name C have high value and getting top. my problem is one condition check Result (Sum(Amount1/Amount2) ) is not equal to zero and null.

Where to apply Sum(Amount1/Amount2) this formula.

Below mentioned my demo data.

Name	Amount1	Amount2	Result
A	100	2000	0.05
B	-30	1000	-0.03
C	0	60000	0
D	2000	-400	-5
E	0	0	#DIV/0!
F	350	0	#DIV/0!
G	-400	200	-2
H	60	12000	0.005
I	35	0.8212	42.62056
J	0.4212	0.763	0.552031
K	12	8	1.5
L	0	-1000	0
M	-90	0	#DIV/0!
N	30	230.993	0.129874
O	62	7782	0.007967
P	800	-32	-25
Q	-23	-7	3.285714
R	100	0	#DIV/0!
S	42	950	0.044211
T	500	8270	0.060459

Re: Using Aggr Function Top N

its_anandrjs — Tue, 22 Jul 2014 06:26:16 GMT

Then Try expression

If( Sum(Amount1/Amount2) = 0, 0, Sum(Amount1/Amount2) )

Re: Using Aggr Function Top N

jagan — Tue, 22 Jul 2014 06:42:09 GMT

Hi Mathew,

Try like this

Top 5 = if(Aggr(rank(Sum({<Amount2-={0}, Amount1-={0}>}Amount2))<=5,Name),Name)

Bottom 5 = if(Aggr(rank(-Sum({<Amount2-={0}, Amount1-={0}>} Amount2))<=5,Name),Name)

Or try like this

Top 5 = if(Aggr(rank(Sum(If(Alt(Amount1/Amount2) <> 0, Amount2))<=5,Name),Name)

Bottom 5 = if(Aggr(rank(-Sum(If(Alt(Amount1/Amount2) <> 0, Amount2))<=5,Name),Name)

Regards,

Jagan.

Re: Using Aggr Function Top N

Tue, 22 Jul 2014 06:47:03 GMT

Hi Mathew,

You can use

Top 5 = if( Amount1/Amount2<>0 and not IsNull(Amount2) and Aggr(rank(Sum(Amount2))<=5,Name),Name)

Thanks,

Garry

Re: Using Aggr Function Top N

muniyandi — Tue, 22 Jul 2014 07:18:57 GMT

Hi Jegan,

First Calculate the sum(Amount1/Amount2) is only number and not equal to zero. after calculate the rank.

Re: Using Aggr Function Top N

muniyandi — Tue, 22 Jul 2014 07:22:07 GMT

Hi Gaurav ,

sum(Amount1/Amount2) <>0 and Not Isnull(sum(Amount1/Amount2) ) function use and again use aggr function . thats my problem.

Re: Using Aggr Function Top N

muniyandi — Tue, 22 Jul 2014 07:44:05 GMT

Hi Jegan,

your are close to reach result. sum(amount1/Amount2)<>0 and not isnull(sum(amount1/Amount2)) how to use your expression.

Thanks.

Re: Using Aggr Function Top N

jagan — Tue, 22 Jul 2014 09:00:50 GMT

Hi,

Try like this

Top 5 = if(Aggr(rank(Sum(If(Alt(Amount1/Amount2, 0) <> 0, Amount2))<=5,Name),Name)

Bottom 5 = if(Aggr(rank(-Sum(If(Alt(Amount1/Amount2, 0) <> 0, Amount2))<=5,Name),Name)

Regards,

Jagan.

Re: Using Aggr Function Top N

muniyandi — Tue, 22 Jul 2014 10:33:49 GMT

Hi Jegan,

top it's fine. I have another dobut on bottom condition calcuation.

Bottom time we have sum of value getting - minus value , again we put front of -minus,

this time ex ( -sum(-500)= +500) how to solve this issues.

we need to write if condition (if (sign(sum(amount2))=-1,sum(amount2),-sum(amount2)) ?

Thanks,

Re: Using Aggr Function Top N

jagan — Tue, 22 Jul 2014 13:46:47 GMT

sum(amount2) * -1

Regards,

Jagan.

Re: Using Aggr Function Top N

jagan — Tue, 22 Jul 2014 13:48:23 GMT

or try

Fabs()

Regards,

Jagan.

Re: Using Aggr Function Top N

muniyandi — Tue, 22 Jul 2014 13:56:25 GMT

Thanks Jagan Fabs()

Re: Using Aggr Function Top N

jagan — Wed, 23 Jul 2014 00:31:30 GMT

Hi Mathew,

Please close this thread by giving Correct Answer to my post, it helps others in finding the answers.

Regards,

Jagan.

Re: Using Aggr Function Top N

muniyandi — Wed, 23 Jul 2014 07:04:46 GMT

Hi Jegan,

Fabs() not working properly for my req.

Ex: Bottom

Data	Rank
-5	1
-3	2
0	3
1	4
10	5
15	6

Fabs(Data)	Rank
0	1
1	2
3	3
5	4
10	5
16	6

Minus value rank position changed. i handled in one more IF condition.

Thanks,

Muni

Re: Using Aggr Function Top N

Wed, 23 Jul 2014 14:08:54 GMT

Adding column to the end of Pivot Table

Hi Jagan,

Request your help in resolving the problem mentioned in above link.

Please let me know if there is any work arround

Thanks

Rajesh

Re: Using Aggr Function Top N

Sun, 16 Oct 2016 08:50:52 GMT

Hi Jagan can we display top 2 values in the below mentioned charts

Creating different kinds of charts and representating data