6 Replies Latest reply on Feb 21, 2016 6:44 AM by James Mclean

    Top N in a category in a country??

    James Mclean

      Hello there,


      Please can someone try to help??


      I would like to create a Top 4 filter that works specific to a each country (5 countries that are being looked at) within a category.


      for example - I have 100 manufacturers across 5 countries within the Soft drinks category.  I would like to know what the 4/5 top manufacturers are within each of the 5 countries for the category.


      Is there a way to create a set/filter/parameter that can do that??


      Please help!


      Thank you!


        • 1. Re: Top N in a category in a country??
          Simon Runc

          hi James,


          Hows this for you? I've recreated your scenario with Ranking products by state from the superstore dataset...


          So I've used the index() function here, as this allows me to determine how this calculation is run, and also order the index.


          So for the index() function (I've called this field [Rank]), I've set this up in the following way...using the 'Edit Table Caluclation' -> 'Advanced' option


          Please note the Sort part

          Please note the Restating every part


          I can the create a parameter, so I can control the top N...and set up a filter calculated field [Show Top N by State Filter] as per the below

          [Rank]<=[Show Top N]


          I then add this to the filter shelf and set to True.


          Hope this is what you were after, and makes sense...if the answer to either of these is 'No' let me know and I can add some further explanations/refinements

          • 2. Re: Top N in a category in a country??
            James Mclean

            Hello Simon,


            Thank you very much for your time and assistance.


            Unfortunately I am still relatively new on operating Tableau and I'm not exactly sure of what/how to execute what you ave explained.  I have started trying and it is becoming a little clearer but not there just yet.


            I will persevere though


            Thank you!

            • 3. Re: Top N in a category in a country??
              Simon Runc

              hi James,


              No problem...so we've used some fairly advanced features here, in terms of Table Calculations (which take a bit of getting used to...but is well worth it!), and especially using the 'Advanced' option


              In brief Tableau has 4 main types of calculation...

              Row Level - Performed 'off canvas' against every Row of the data eg. IIF([Sales] > 1000, 'Good', 'Bad')

              Aggregated - Aggregates the result based on the Level of Detail in the Viz eg. SUM([Sales])

              Table Calculations - Aggregated Calculations (and can be Aggregate on Aggregate), where we can define (by using the Addressing and Partitioning) how the calculation will be run

              Level of Detail (LoD) - I won't confuse things here with a full explanation, but are a bit of the above 3!


              index() is a Table Calculation. As such by setting it up in the way I have I have asked it to create the index (which is just a numbering) over State and Product, Sort them on Sales, and restart the numbering every State.


              It's probably worth you watching the On-demand training videos on Table Calculations (only about 10-20 mins)...which will give you a good introduction to what they are, and then I'd watch this brilliant Video (about an hour) which should give you some more detail on what they are doing (and why)


              TDT with Jonathan Drummey - July 10, 2014


              if you still have any questions on the post following this, please post back and I'll be happy to help

              • 4. Re: Top N in a category in a country??
                Kent Sloan

                Hi James,


                This might not cover your situation but below is an example of a much simpler way to do this by setting your category filter to "Add to Context" .





                Starting with a list of sales by country I add a filter to display the top ten countries by sum of sales by placing country on the filters shelf.



                But now if I add in market as a filter only the 4 countries from the APAC region that are in the overall top 10 are displayed.



                This can be fixed by letting Tableau know that the Market filter should be applied before calculating the top 10. This can be done by right clicking on the market filter and selecting "Add to Context".



                The Market filter will now be gray to indicate that it is a in-context filter which runs before all other filters are applied.

                • 5. Re: Top N in a category in a country??
                  James Mclean

                  Hi Kent,


                  Thanks ery much for the feedback! Apologies for the delayed response.


                  I will give you method a try as didnt have time last week to test these solves and had to do a lot of manual work in excel.


                  Thanks again for helping!




                  • 6. Re: Top N in a category in a country??
                    James Mclean

                    Hello Simon,


                    Thanks very much for all the help on this!  Haven had a chance to test all sultion provided but will definitely be try them during the coming week!


                    Thank you for taking the time to help!