Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Top N in Pivot/Straight table with multiple dimension

Hi All,

I am sorry for asking this question which has been mentioned many times in community but the problem is that i simply can't apply those solution as i am newbie in QlikView.

In my situation i have one pivot table which shows the Bank Branch name, its account numbers and respective balance of each account. now i want to display the top 10 balances and their account holder in table , don't know that it should be pivot table or straight table can do this? in future i also want to add the account type in the list. So please guide me how to achieve this?

As mentioned in Excel, i want to have e.g. top 3 account holders, balance wise.

17 Replies
Not applicable
Author

Thanks Jebamalai, but can you convert this to pivot table. e.g. if user want to first see the branch and when it expands it has to see the top 5 accounts and their balances?

tresesco
MVP
MVP

May be like attached sample. calculated second dimension like:

=Aggr(If(Rank(Sum(Balance))<6, AccountNo), BranchID, AccountNo)

Anonymous
Not applicable
Author

We don't have an inbuild option to restrict the Top 10 in pivots.However I will check the other options and get back to you

Not applicable
Author

For Pivot, Use this calculated dimension,

Dim1 : if(Aggr(Rank(sum(Balance)),AccountNo)<=3,AccountNo)

Dim2 : branch name

Exp:  sum(balance)

Thanks,
Prabhu

tresesco
MVP
MVP

Have you checked my attachment above?

Not applicable
Author

Skhan,

you may use rank with set analysis.

Try with expression:

sum({<AccountNo={"=rank(sum(Balance))<=10"}>}Balance)

You may also use more dynamic approach.

Look into attached sample

Not applicable
Author

unfortunately can't open as i am using free version now. will appreciate if you can describe your solution here.

tresesco
MVP
MVP

Dim1 : BranchID

Dim2: =Aggr(If(Rank(Sum(Balance))<6, AccountNo), BranchID, AccountNo)         // for top5

Exp: Sum(Balance)