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

[its_anandrjs]

Then Try expression

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

[jagan · Accepted solution]

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.

[#4]

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

[muniyandi]

Hi Jegan,

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

[muniyandi]

Hi Gaurav ,

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

[muniyandi]

Hi Jegan,

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

Thanks.

[jagan]

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.

[muniyandi]

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,

[jagan]

Hi

sum(amount2) * -1

Regards,

Jagan.