Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hi
check the attach example
if you use version 11 you can use the left table other wise use the middle table
hi
check the attach example
if you use version 11 you can use the left table other wise use the middle table
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
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
Thanks For All,
Actually i used wrong field Now i corrected, instead of using INV_C_MARKET_PS field i used REGION_PS
Chandu