17 Replies Latest reply: Apr 23, 2014 6:18 AM by Tresesco B

# 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.

• ###### Re: Top N in Pivot/Straight table with multiple dimension

Skhan,

regards

Darek

• ###### Re: Top N in Pivot/Straight table with multiple dimension

You can use the straight table.

Go to "Dimension Limits".Here you can restrict the values.

Let me know if this doesn't worked

• ###### Re: Top N in Pivot/Straight table with multiple dimension

If you use straigth table you get 'Dimension Limit' in it, that makes it easier for you to show the top N values. The similar can be achieved using Pivot table as well with an overhead of complexer expression which would probably require Aggr() and Rank(). Hence, if you don't need pivot for a reason go with straigth table and use dimension limit.

• ###### Re: Top N in Pivot/Straight table with multiple dimension

HI,

please have a look on below points,

1.  You can use the Straight table for this and the Dimension Limit tab will help you.

2. or you can write the Expression for top values.

3. if you want to do it with the pivot table then you have to use the Aggr() function in the expression.

-Nilesh

• ###### Re: Top N in Pivot/Straight table with multiple dimension

i will really appreciate if you can quote an example based on my scenario.

• ###### Re: Re: Top N in Pivot/Straight table with multiple dimension

Hi,

Please find the attached for straight table.

Hope it help

Regards

ASHFAQ

• ###### Re: Top N in Pivot/Straight table with multiple dimension

Thanks a log Ashfaq. 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?

• ###### Re: Re: Top N in Pivot/Straight table with multiple dimension

May be like attached sample. calculated second dimension like:

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

• ###### Re: Top N in Pivot/Straight table with multiple dimension

Hi skhan

Pls check this word document,where in I have imported your data in to a table and restricted only the Top 10.

Let me know if this helps

TR

Jeba

• ###### Re: Top N in Pivot/Straight table with multiple dimension

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?

• ###### Re: Top N in Pivot/Straight table with multiple dimension

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

• ###### Re: Top N in Pivot/Straight table with multiple dimension

cheers!!!

• ###### Re: Top N in Pivot/Straight table with multiple dimension

For Pivot, Use this calculated dimension,

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

Dim2 : branch name

Exp:  sum(balance)

Thanks,
Prabhu

• ###### Re: Top N in Pivot/Straight table with multiple dimension

Have you checked my attachment above?

• ###### Re: Top N in Pivot/Straight table with multiple dimension

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

• ###### Re: Top N in Pivot/Straight table with multiple dimension

Dim1 : BranchID

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

Exp: Sum(Balance)

• ###### Re: Top N in Pivot/Straight table with multiple dimension

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