2 Replies Latest reply on Oct 29, 2012 6:57 PM by Dan Murray

    Top 10 filter

    Ajit Kumar


      I have created a workbook where I am giving Country and their Brand information.


      I have filtered the brand on the basis of one measure  called Stock listed that is Top 10 on the basis of Stock Listed.

      I have given the filter for Country. When I am selecting any one country I am not able to see the Top 10 brands of that Country. It shows only 2 or 3 brands for countries. Ideally it should show for each country 10 brands.


      I have attached my workbook.


      Please help me on this

        • 1. Re: Top 10 filter
          Alex Kerin

          You can use your rank calculation to do this, as long as the partitioning and sort is set correctly:



          • 2. Re: Top 10 filter
            Dan Murray



            Here's a flexible way to deal with this sorting issue.  The reason your sort isn't working properly is that you are sorting on the entire dataset not the combination of country and brand.  While we're at it lets make that rank flexible.


            First create a set that is the combination of country and brand by multi selecting those two dimensions, right-clicking, and creating the set. Them drag the resulting set into your into a bar chart in which you display the stock measure in the bar and the combination of country and branding on the row shelf. Then, drag your just created set between the county and brand pills on your row shelf.  Then right-click on the set pill and sort descending.  You should see the bars align with the stock sorted by descending stock levels within each country.


            Now, if you wand to create a flexible rank filter that will allow the info consumer to see a top n filter by country do this.


            1. Create in index calculation

            2. Drag the resulting calculation to the label shelf so you can see what's happening

            3. Edit the index to calculate based on the combination of country, then brand

                at the level of brand

                Restarting every country

            4. Control frag the resulting index to the filter shelf

            5. Exposé the quick filter

            6. Select top 10, top5, bottom three....whatever.


            I would have built an example but I'm on a plane doing this on an iPad.  If this isn't clear just ping me at Dan.murray@interworks.com and I will provide an example on this thread.