topic Re: Using Rank in QlikView

Using Rank

Wed, 14 Sep 2016 09:50:26 GMT

Hi all,

Below is the table i am using

Customer name	Sale
A1	938
A2	324
A3	302
A4	463
A5	427
A6	0
A7	277
A8	387
A9	0
A10	183

i need create two tables for top 5 and bottom 5 . In bottom 5 i need to exclude whose customer having "zero" in their sales . User is flexible to select any top n or bottom n b/w 5 (i.e., top 1-2 or bottom 2-3 or top 3-4) .

Could any one help me .

Thanks in advance

Re: Using Rank

swuehl — Wed, 14 Sep 2016 16:07:41 GMT

Create two tables, Customer name being dimension in both.

Then as expression

=Sum({<CustomerName = {"Rank(Sum(Sales))<=$(vTopRank)"}>}Sales)

resp.

=Sum({<CustomerName = {"Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(vBottomRank)"}>} Sales)

Re: Using Rank

Wed, 14 Sep 2016 17:37:36 GMT

Hi Stefan,

Thanks for reply..

could you kindly provide me the expressions for $(vTopRank) and $(vBottomRank).

But however, the above expression helps me to filter out the data according to top or bottom N. but my requirement is if i wanna to see only top 2 and 3 customer sales or bottom 2 and 3 customer sales .

Thanks in advance

Re: Using Rank

swuehl — Wed, 14 Sep 2016 18:00:16 GMT

vTopRank and vBottomRank are just variables that hold a number (Top or Bottom N customers).

I thought that's what you intended (creating e.g. a slider so the user can select N)

I've also noticed I forgot the leading equal sign in the set modifier to indicate an advanced search:

=Sum({<CustomerName = {"=Rank(Sum(Sales))<=$(vTopRank)"}>}Sales)

resp.

=Sum({<CustomerName = {"=Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(vBottomRank)"}>} Sales)

Re: Using Rank

Thu, 15 Sep 2016 12:12:47 GMT

Hi stefan,

Thanks for previous reply..

But when i using ur bottom expression(for bottom 4 customer) . the output is show below

but the user wanna to only bottom 2 to 4 records

that is, he wanna to see only

Kindly help me ..

Thanks in advance

Re: Using Rank

swuehl — Thu, 15 Sep 2016 16:20:51 GMT

You can add a range to the condition like

=Sum({<CustomerName = {"=Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(Max) and Rank(-Sum({<Sales = {'>0'}>} Sales))>=$(Min)"}>} Sales)

Re: Using Rank

Thu, 15 Sep 2016 16:55:16 GMT

hi,

Thanks a lot ...

Re: Using Rank

Tue, 20 Sep 2016 07:49:43 GMT

If we want to see along with zero values for bottom customers ?

i have removed >0 from the set analysis but i m not getting correct values .

Thanks in advance

Re: Using Rank

Tue, 20 Sep 2016 13:45:49 GMT

Hi ,

i have a varable

vProdVar= sum(aggr(if(IsNull(Map_Customername) or not( IsNull(Map_Key)),'',TotalMargin),Key,[customer name]))+sum(aggr(if(IsNull(Mapping_Customername) or not( IsNull(Mapping_Key)),'',[Total margin]*-1),Key,[customer name]))

in straight table .. i m using the below dimension for selecting bottom customers.

AGGR(IF(Rank(-$(vProdVar),4)>=$(vMin) AND

(Rank(-$(vProdVar),4)<=$(vMax)) ,[customer name] ),[customer name] )

i am getting error with rank function . Could you kindly help me ..

Thanks advance

Re: Using Rank

swuehl — Tue, 20 Sep 2016 17:08:09 GMT

Not sure what your complex variable should return.

Could you post a small sample QVW that demonstrates also your data model?