5 Replies Latest reply on Oct 16, 2013 12:33 AM by Norman Dilthey

    Nested ranking for 2nd, 3rd, 4th etc. best result

    Norman Dilthey



      I have a little issue I cannot solve myself although this is probably quite simple...


      Attached is an image of what the data looks like and what I would like to see in different sheets.


      Basically I have country data with different products (sales). Now on separate sheets I would like to show the country with most sales broken down by product, 2nd highest sales again broken down by product etc... Top 5 countries on 5 individual sheets.


      I hope I have provided enough information..


      Please help .


      Thank you!


        • 1. Re: Nested ranking for 2nd, 3rd, 4th etc. best result
          Shawn Wallwork

          Norman, you can do this by creating an Index calculation. (See attached.) Let me know if you have other question.





          1 of 1 people found this helpful
          • 2. Re: Nested ranking for 2nd, 3rd, 4th etc. best result
            Norman Dilthey

            Hi Shawn,


            thanks for you reply. However I don't get it to work properly. When I expand country and region, nothing appears for the fourth item in the ranking. If I change it to the second (china) and expand, country changes to Mexico & Pakistan. The same happens in my data. So this does not work either unfortunately


            Even though the index calc is set to calculate per Country / Region, USA and China and many others are under index 1, which I don't get.





            • 3. Re: Nested ranking for 2nd, 3rd, 4th etc. best result
              Shawn Wallwork

              Oh, now you tell me, you want drilling too! Post a packaged workbook with sample data and your viz set up the way you want it (described it) and I'll be happy to take another look at this.





              • 4. Re: Re: Nested ranking for 2nd, 3rd, 4th etc. best result
                Jonathan Drummey

                Shawn - I'd been working on a reply on Friday but an overaggressive spam filter was eating it, I have a solution solves the drilling problem.


                Norman - Table calculations and drilling often don't work very well together, depending on what you want there might be a solution using that route or not. In addition, you didn't specify the type of data source, size of your data, number of products, etc. so a table calculation filter may not be the best route to go in any case. That was my first choice (because it's often the easiest), I set up a version in the TC Filter worksheet in the attached but like Shawn's workbook it's not drillable. When we drill down a hierarchy, the expanded elements are automatically added to the partitioning of the table calculation and for this particular table calculation filter cause it to return a lot more results.


                Here are some other options that would require a lot more effort: You could create a separate data source that returns the top countries, and use that blended source as a filter for Tableau, or to do something in the underlying query. Many data sources have a built-in rank function that could be performed in the data source and enable the rank to simply become another column in your data.


                Finally, here's an option inside Tableau: In the attached (using Superstore Sales as a model), I set up three Sets to get the Top 1, 2, and 3 countries, then a calculated field that looks at to return the particular top country - see the three Top Country worksheets for examples. Each country is now completely drillable down to State/City/Postal Code, and each Product Category is drillable as well. This does require using a data source that can support Sets in calculated fields (Tableau data extract, etc.). Also, depending on your data volume, this may not be performant as it will cause Tableau to issue a number of subqueries (I understand that Oracle and MySQL can have problems here).


                Finally, when you have separate worksheets, Tableau will have to issue separate queries for each worksheet and that can result in cache misses, making for slower performance. Using a single worksheet with all the data on it and letting the user choose the filter, may be your best bet.



                • 5. Re: Nested ranking for 2nd, 3rd, 4th etc. best result
                  Norman Dilthey

                  Thank you so much Jonathan!


                  Your solutions were very very helpful!! I ended up putting the 5 best countries into one table which made everything a little easier, but still I appreciate your help in finding a solution!