Top N in Pivot/Straight table with multiple dimension

[#1]

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.

[#2]

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]

May be like attached sample. calculated second dimension like:

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

[Anonymous]

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

[#5]

For Pivot, Use this calculated dimension,

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

Dim2 : branch name

Exp:  sum(balance)

Thanks,
Prabhu

[tresesco]

Have you checked my attachment above?

[#7]

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

[#8]

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

[tresesco]

Dim1 : BranchID

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

Exp: Sum(Balance)