Top N in Pivot table with 2 or more dimensions

[montubhardwaj]

Hi all and hope everyone is good.

I have this issue with Pivot table where I have to list top 5 Products (based on sales) with 2 dimensions. It works good with one dimension but when I add two dimenstion, I am getting all the products and not just the top 5. See below pic for the desired layout. I know that I have to restrict my dimensions using RANK and AGGR functions so that only top products are shown but this is not working for me. Any suggestions/ideas? Thanks for your help.

[#2]

Hi,

If you are using QV 11 then you can limit the dimension else use following:

IF(Aggr(Rank(Sum(PROD),4,1),Distributor)<5,Distributor)

Use this in your dimension. Also set property to supress when value is NULL.

Tip: IF it is not working use 6 instead of 5

[montubhardwaj]

Hello Kirti. Thanks for looking into it. As I said, I will be using three dimesnsions (Distributors, Customers and Products ). I am confused about writing the calculated dimesntion. Not sure what I should write in and for which dimension. When I did for Products, it is still showing me all the products

[#4]

tou have to add this for all dimensions..

IF(Aggr(Rank(Sum(PROD),4,1),Distributor)<5,Distributor) ------likewise distributor you to change the other dimensions too

[montubhardwaj]

But this will restrict even distributors to top 5. I just want to have top 5 Products (PROD1-PROD5) listed for all the distributors. Lets say we have 50 Distributors. So for all 50 of them, we want to have top 5 products listed.

I am able to fighure out the dimension part but by default, top products are not listed. If I make a seletion on Distributor, then only right values/results are showing up. I want to show top 5 by default without any selections on Distributors.

[#6]

ok..so in this case you dont have to restrict dimensions...You have to first find out 5 top dimensions in ETL. Store that in Variable

v_1=Prod1

v_2=Prod2.........

then create five expressions

1st Expression  Sum({<Product={'$(v_1)'}>}  Dollar Value)

2nd Expression  Sum({<Product={'$(v_2)'}>}  Dollar Value)..............

[montubhardwaj]

Thanks for the suggestion but I very much want to do it in the front end. If I want to do it in the back-end, it is going to take lot of time because of the nature of script used.

[#8]

I think with front end it is not possible. i have faced same issue and i resolved by the above suggested way only..