Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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.
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)..............
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.
I think with front end it is not possible. i have faced same issue and i resolved by the above suggested way only..