Using Aggr Function Top N

[muniyandi]

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

[jagan]

or try

Fabs()

Regards,

Jagan.

[muniyandi]

Thanks Jagan Fabs()

[jagan]

Hi Mathew,

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

Regards,

Jagan.

[muniyandi]

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

[#6]

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

[#7]

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

Creating different kinds of charts and representating data