topic Problem with Rank in QlikView

Problem with Rank

Tue, 21 Feb 2012 12:21:34 GMT

Dear All,

want to display region wise top five customers sales and rest of other customer sales with label "Others", when i writen rank with aggr in pivot table its giving wrong no. of customers and sales value.

please give a solution.

for your reference i attached the file, in that file contains 2 table green table is containing the requirement and blue table contans data for validation

Thanks in advance

chandu jak

Re: Problem with Rank

lironbaram — Tue, 21 Feb 2012 12:47:33 GMT

check the attach example

if you use version 11 you can use the left table other wise use the middle table

Problem with Rank

Tue, 21 Feb 2012 12:53:48 GMT

Hi Chandu,

in your expression you ranked along a wrong field: see below for correction:

=If(Aggr(Rank(Aggr(Sum([INV_N_QUANTITY_PS]), REGION_PS,DISTRIBUTOR_ID_PS),4,1), INV_C_MARKET_PS ,DISTRIBUTOR_ID_PS) <= 5,DISTRIBUTOR_ID_PS)

Regards, Roland

Problem with Rank

swuehl — Tue, 21 Feb 2012 13:00:43 GMT

And I think you could shorten the dimension expression a bit (inner aggr() not needed):

=Aggr(if(Rank(Sum([INV_N_QUANTITY_PS]),4,1 <=5,DISTRIBUTOR_ID_PS),INV_C_MARKET_PS,DISTRIBUTOR_ID_PS)

If you set the NULL representation in presentation tab to OTHERS, it looks quite nice.

Regards,

Stefan

Problem with Rank

Tue, 21 Feb 2012 13:09:25 GMT

Thanks For All,

Actually i used wrong field Now i corrected, instead of using INV_C_MARKET_PS field i used REGION_PS

Chandu