3 Replies Latest reply on Mar 12, 2012 5:48 PM by Joe Mako

    Top10 Bottom10 with extra info

    Gabriel Gejman

      Hi All,

       

      I have an issue with a workbook I'm preparing. I have a list of 200 stores with thousand of rows classified in 3 categories and each row having a time. I want to show the Top10 (lower time) and Bottom10 stores by AVG(Time) and at the same time show the percentage of each category for that store.

       

      I used something similar to Finding the Top N Within a Category to create the Top10, as I want the Top10 of the whole table, not by category, but the Bottom10 is not working correctly. I need to be able to filter by Region or District and show the Top10, Bottom10 only for the selected fields.

       

      Top10 looks like this:

      and Bottom10 looks like this:

      Is the same Sheet but one is sort Ascending and the second Descending on Store by Avg(Time).

       

      If you have any idea would be really appreciated. Attached is the workbook.

        • 1. Re: Top10 Bottom10 with extra info
          Joe Mako

          That KB article is very old, that was an option in version 5, before custom table calculations. While that method still works in some situations, I would not consider it ideal in any.

           

          Also you have an interesting situation, with multiple mark types, where one of them uses the color shelf, and not all possible colors exist for all stores. That combination requires consideration for a number of factors that effect table calculation evaluation. To use a table calc filter in your situation would require a few complexities.

           

          Instead, just make use the Top N Filter. Simply place "Store #" on the filter shelf, and from the "Top N" tab, select your top/Bottom 10 based on the Avg of "Time", and you will get two worksheets with what you have asked for.

           

          If you need additional filters, you will want to be aware of the order of operation in Tableau, see http://community.tableau.com/message/139603#139603 for more details.

          • 2. Re: Top10 Bottom10 with extra info
            Gabriel Gejman

            Thanks Joe!

             

            As you mentioned I do need to be able to filter by Region/District, and using those filters as "Context" filters solved the problem until I put them on a Dashboard and have other graph filter the Top/Bottom 10 using actions. Seems like  actions are on a lower priority level for filtering even does fields are "Context" filters.

            • 3. Re: Top10 Bottom10 with extra info
              Joe Mako

              Yes, action filters only exist the category "3. General Filters on Non-Aggregated Pills" because actions only work on non-aggregated dimensions.