8 Replies Latest reply on Nov 9, 2018 2:55 PM by Dilip B

    Top N by excluding the dimension from the table

    Dilip B

      Hello - I need to display Sum of Revenue of Top N Products within each quarter (refer to the data and output tables below) but without displaying the Product pill on the table/graph. I was able to get the Top N within each Product using the Rank function and by applying the table calc at Quarter and Product level but when i remove the Product pill from the display the Rank function blows up (which make sense).  I couldn't get it to aggregate the Revenue of Top N products at the Quarter level.

       

       

      DateProductRevenue
      Q1 2012A100
      Q1 2012B200
      Q1 2012C300
      Q2 2012A100
      Q2 2012B25
      Q2 2012C75

       

       

      Output  -   Top 2 Products Total Revenue in each Quarter 

      Date
      Revenue
      Q1 2012500
      Q2 2012175

      So basically  in Q1 it will be sum of B & C Products  - 500 and in Q2 it will be sum of A& C  - 175

       

      Attached the sample work book here.

       

      Tried few things suggested in these discussion but without success

      Ranking Excluding Dimensions

      Top N with multiple dimensions displayed

       

      Any help on this would be really appreciated.

       

      Thanks

      Dilip