1 Reply Latest reply on Feb 12, 2013 12:31 PM by Jim Wahl

    Creating "Other" Bin of smallest measures

    Li-Kim Lee

      Hello all,

       

      Suppose one had a dimension which had about 8 to 10 variables and wanted to aggregate all of the 4 (say) smallest measures (sum) into a new bin called "Other", how would one do that?  I would like my viz to only show individual values for the largest 5 variables and aggregate all the others into a single value of "Other".

       

      My intent is a version of the solution posted on http://kb.tableausoftware.com/articles/knowledgebase/creating-variable-sized-bins, except that the variables are unspecified and hence dynamically generated.

       

      Many thanks in advance

      LKL

        • 1. Re: Creating "Other" Bin of smallest measures
          Jim Wahl

          I'm not quite sure what you mean when you say dimensions, variables, measures, but I think what you want to do is create an other bin for smallest dimension fields?

           

          For example, if you have a product dimension with 10 product names, show the top five by sales and group the remaining five in "other products"?

           

          The attached workbook does this and is based on Joe Mako's excellent description here (http://community.tableau.com/message/168033#168033).

           

          Here is the process I used to create the view

          1. Create two parameters:

          1.1. Choose top # products -- integer -- parameterizing this will make it easier to test

          1.2. Show all products -- boolean -- expands the other category on demand

           

          2. Create calculated fields:

          2.1. In top n = INDEX() <= [Choose top # products]

          2.2. Products (top) =

          IF [In top n] THEN ATTR([Product])

          ELSEIF [Show all products] THEN ATTR([Product])

          ELSE STR(SIZE() - [Choose top # products]) + " others" END

          You're duplicating the product dimension here and creating the "other category"

           

          2.3 Display values (sales) =

          IF [In top n] OR [Show all products]

          THEN SUM([Sales])

          ELSEIF INDEX() == [Choose top # products]+1

          THEN WINDOW_SUM(SUM([Sales]),0,LAST())

          END

          The WINDOW_xxx part of this function is what sums the others.

           

          2.3 Display rank = IF [In top n] THEN INDEX() ELSE [Choose top # products]+1 END

          This will push the "others" to the bottom.

           

          3. Putting it all together

          3.1. Right click on parameters and select "show parameter control"

          3.2. Put product on the level of detail shelf.

          3.3. Put Display values (sales) on the columns shelf.

          3.4. Put Display rank on the rows shelf.

          3.5. Put Products (top) on the rows shelf.

          3.6. Select Compute using "Product" for both the pills added in 3.3, 3.5 and 3.6

          3.7. On the Product pill, select Sort > Descending , Sales SUM

          3.8. Add "In top n" to color shelf. Change the colors. Select Sort > Manual with True first

           

          4. Cleanup

          4.1. On Display Rank pill, unselect "Show header" to hide the rank value.

           

          Again, check out Joe's post for more detail and options, including multiple levels of dimensions, ...

           

           

          Jim

          1 of 1 people found this helpful