Problem with Rank

[#1]

Dear All,

i

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

[lironbaram · Accepted solution]

hi

check the attach example

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

[#3]

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

[swuehl]

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

[#5]

Thanks For All,

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

Chandu