Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
muniyandi
Creator III
Creator III

Using Aggr Function Top N

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.

NameAmount1Amount2Result
A10020000.05
B-301000-0.03
C0600000
D2000-400-5
E00#DIV/0!
F3500#DIV/0!
G-400200-2
H60120000.005
I350.821242.62056
J0.42120.7630.552031
K1281.5
L0-10000
M-900#DIV/0!
N30230.9930.129874
O6277820.007967
P800-32-25
Q-23-73.285714
R1000#DIV/0!
S429500.044211
T50082700.060459
15 Replies
jagan
Luminary Alumni
Luminary Alumni

or try

Fabs()

Regards,

Jagan.

muniyandi
Creator III
Creator III
Author

Thanks Jagan Fabs()

jagan
Luminary Alumni
Luminary Alumni

Hi Mathew,

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

Regards,

Jagan.

muniyandi
Creator III
Creator III
Author

Hi Jegan,

Fabs() not working properly for my req.

Ex: Bottom

Data

Rank

-51
-32
03
14
105
156
Fabs(Data)Rank
01
12
33
54
105
166

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

Thanks,

Muni

Not applicable

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

Not applicable

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

Creating different kinds of charts and representating data