topic Re: Top N in Pivot/Straight table with multiple dimension in QlikView

Top N in Pivot/Straight table with multiple dimension

Wed, 23 Apr 2014 07:22:19 GMT

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

Wed, 23 Apr 2014 07:33:03 GMT

Skhan,

please share qvw or xls with your data sample.

regards

Darek

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

Anonymous — Wed, 23 Apr 2014 07:36:53 GMT

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

tresB — Wed, 23 Apr 2014 07:39:02 GMT

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

nilesh_gangurde — Wed, 23 Apr 2014 07:39:44 GMT

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

Wed, 23 Apr 2014 07:52:50 GMT

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

ashfaq_haseeb — Wed, 23 Apr 2014 07:59:10 GMT

Hi,

Please find the attached for straight table.

Hope it help

Regards

ASHFAQ

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

Anonymous — Wed, 23 Apr 2014 08:01:44 GMT

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

Jeba

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

qlikpahadi07 — Wed, 23 Apr 2014 08:37:48 GMT

Might be helpful

cheers!!!

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

Wed, 23 Apr 2014 09:31:50 GMT

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: Top N in Pivot/Straight table with multiple dimension

Wed, 23 Apr 2014 09:32:48 GMT

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: Re: Top N in Pivot/Straight table with multiple dimension

tresB — Wed, 23 Apr 2014 09:40:00 GMT

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

Anonymous — Wed, 23 Apr 2014 09:46:22 GMT

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

Wed, 23 Apr 2014 09:47:45 GMT

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

tresB — Wed, 23 Apr 2014 09:51:10 GMT

Have you checked my attachment above?

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

Wed, 23 Apr 2014 10:01:18 GMT

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

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

Wed, 23 Apr 2014 10:07:30 GMT

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

tresB — Wed, 23 Apr 2014 10:18:42 GMT

Dim1 : BranchID

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

Exp: Sum(Balance)