Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
kristeljoymalapitan

How to get top five with the highest rank?

Good Day,

How can I get the top 5 Region with the highest outstanding balance percentage per month in a line graph, please see below sample screenshot

 

I use this measure  : SUM({<[CLASSIFICATION] = {'PD','LEGAL'}>}[outstanding_bal])/(SUM({<[CLASSIFICATION = {'PD','LEGAL','REGULAR'}]>}[outstanding_bal])

clipboard_image_0.png

 

Thank you

 

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

May be this

Aggr(

If(Rank(Sum({<[CLASSIFICATION] = {'PD', 'LEGAL'}>} [outstanding_bal])/Sum({<[CLASSIFICATION] = {'PD', 'LEGAL', 'REGULAR'}>} [outstanding_bal])) < 6, Sum({<[CLASSIFICATION] = {'PD', 'LEGAL'}>} [outstanding_bal])/Sum({<[CLASSIFICATION] = {'PD', 'LEGAL', 'REGULAR'}>} [outstanding_bal]))

, MONTHYEAR, REGION)

View solution in original post

11 Replies
sunny_talwar

So the Region can be different top 5 in months? or do you want to show the same top 5 regions across all months?

asinha1991
Creator III
Creator III

did you try dimension limit tab in property and select first expression top 5?

 

if you have to do it at dimension level for some reason, use this in dimension

 

aggr(if(rank(SUM({<[CLASSIFICATION] = {'PD','LEGAL'}>}[outstanding_bal])/(SUM({<[CLASSIFICATION = {'PD','LEGAL','REGULAR'}]>}[outstanding_bal]))<5,Region),Region)

kristeljoymalapitan
Author

I need to get the top 5 region per month.so it could be different regions top 5 per month

deepakahirwar
Partner
Partner

Top 5 Values:

 

SUM({<Product = {"=Rank(SUM({<Type = {'Product'}>}Value),4)<=5"}>}Value)

 

Bottom 5:

 

SUM({<Product = {"=Rank(-SUM({<Type = {'Product'}>}Value),4)<=5"}>}Value)

kristeljoymalapitan
Author

this is not working due to error in expression.

Surya
Creator II
Creator II

Hello,

As per my understanding .

u have 2 dimesions..

goto properties > dimension limits >select 2nd dimension > show >only largest 5

I think it will work

sunny_talwar

May be this

Aggr(

If(Rank(Sum({<[CLASSIFICATION] = {'PD', 'LEGAL'}>} [outstanding_bal])/Sum({<[CLASSIFICATION] = {'PD', 'LEGAL', 'REGULAR'}>} [outstanding_bal])) < 6, Sum({<[CLASSIFICATION] = {'PD', 'LEGAL'}>} [outstanding_bal])/Sum({<[CLASSIFICATION] = {'PD', 'LEGAL', 'REGULAR'}>} [outstanding_bal]))

, MONTHYEAR, REGION)
kristeljoymalapitan
Author

Hi Surya,

 

It works, however how can I assure that I got the highest 5 rank?

What expression to be use?

 

Thank you

kristeljoymalapitan
Author

Thanks Sunny_talwar, it works! 🙂