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

# Top N by excluding the dimension from the table

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

Any help on this would be really appreciated.

Thanks

Dilip

• ###### 1. Re: Top N by excluding the dimension from the table

Hi,

I believe the attached workbook resolves your issue. I created a parameter that allows for a sliding value from 1-5, and then dragged Product to the filter tab, and edited the filter using the Top tab, and using the parameter as the TOP value. I then either used quantity or revenue, depending on the desired ranking value. In the attached workbook I have two worksheets, one showing revenue ranking and one showing quantity ranking.

• ###### 2. Re: Top N by excluding the dimension from the table

Hi Dilip,

You may want to put the [Top 5] Table Calculation Filter

inside another (Nested) Table Calculation for Top 5 Revenue.

Yours,

Yuri

• ###### 3. Re: Top N by excluding the dimension from the table

Hello Yuriy,

I had to do it SQL by writing views to accomplish this so haven't had a chance to look at your solution until now (sorry for the delay).

Your solution works perfectly fine for the above requirement. Now the requirement has changed a little bit. To be able to apply a filter by Month and have the top 5 for month as well. I'll try this to tweak your solution and see how I can make it make it work for both Quarter and Month.

Appreciate for taking time to resolve my problem.

Dilip

• ###### 4. Re: Top N by excluding the dimension from the table

Hi Ken,

-Dilip

• ###### 5. Re: Top N by excluding the dimension from the table

Dilip, you're welcome.

• ###### 6. Re: Top N by excluding the dimension from the table

Hi Dilip,

Sorry about that, I've exported my solution as 10.4 and attached below if that's easier.

Thanks,

Ken

• ###### 7. Re: Top N by excluding the dimension from the table

Hi Ken,

Thank you for providing the workbook in 10.4 and for taking time to help me out. The logic is not working as expected. For example  Q1 2012 the quantity on your logic is showing as 1,470,754 whereas it should be 1,523,585. Looks like its not considering the Top 5.

 1,523,585