3 Replies Latest reply on Feb 25, 2013 8:36 AM by Kristin Chula

    Top n records filter with a dimension on the color shelf shows more than n records

    Kristin Chula

      I want to show the top 10 customers, ranked by the sum of their sales as a bar chart.

      In the example I posted, I created a new calculation using the INDEX() function, and sorted it on Customer ranked in descending order based on the sum of Sales.

      Then I made a parameter where the user can type in how many top records they want to see, and finally another calculated field that compares the user-entered value with the INDEX() calculation that I used as a filter.

      This works just as intended.

       

      However, moving on the the next tab of the example I added Container to the color shelf.  I updated the INDEX() calculation using the advanced functions - adding Container to the "compute using" box, and using Customer in "at the level" box.

      Now each Customer has a unique INDEX value, but they are ordered in some way I can't understand.  It is also displaying 13 customers instead of 10 - although the top 10 match up with the first tab.  The best I can tell the trouble starts when each bar does not have exactly the same amount of colors in it.

       

      Any ideas?  Is there a better way to make this work?

        • 1. Re: Top n records filter with a dimension on the color shelf shows more than n records
          Joshua Milligan

          Kristin,

           

          I love table calculations and their ability to solve all kinds of interesting problems!  In this case, however, a table calculation is not needed and, in fact, will cause you some issues (as you've seen).

           

          For what you are trying to do, a simple row level filter will work.  Simply drag customer to the Filters shelf, and filter by Top N (you can use your parameter here too.)  Hopefully this helps!

           

          Regards,

          Joshua

           

           

          01.png

          02.png

          • 2. Re: Top n records filter with a dimension on the color shelf shows more than n records
            Joshua Milligan

            Kristin,

             

            Just a couple of notes on using table calculations as filters, because there are times when it is helpful:

             

            1. Row calculations are performed at the data source level (usually by the underlying database).  So only data that meets the filter condition is ever returned to the Tableau data engine.

             

            2. Table calculations are done after the data returns to the Tableau data engine.  So table calculations used as filters happen after the data has returned to Tableau.

             

            You can use this fact in lots of pretty neat ways.  Here are a couple of examples:

            1. If you want to only show a few categories, but still want a Grand Total to show the total amount of all categories, you can use a table calculation to filter the categories you want.  The Grand Total will still reflect all the data.  Use:

            LOOKUP(ATTR([Category]), 0) as the filter.

            2. The same is true if you want a few categories to display, but still want to show the percent of the total for all categories.

            3. If you want to rank sub-categories under a category, you can use a technique similar to what you did above and restart the table calculation for every category.  You could filter on that too.  However, keep in mind that Index() is not always the best for ranking because it ignores ties.  You might search the forums for some other examples of ranking techniques.

             

            Regards,

            Joshua

            1 of 1 people found this helpful
            • 3. Re: Top n records filter with a dimension on the color shelf shows more than n records
              Kristin Chula

              Thank you for you explanation and advice.  I have some dashboards where your hint on the LOOKUP function will definitely eliminate some headaches!

               

              On the original question, is there a way to take this example 1 step farther, and have 10 bars of unique, top 10 Customers, and an 11 row that aggregates the of the customers into an "all others" bar?