6 Replies Latest reply on Jul 19, 2016 3:15 PM by Melissa Fanucci

    Tips for a newbie on grouping data

    Melissa Fanucci

      Hi all!

       

      Week two of playing with Tableau. I've got the following table showing various commodities crossing the border. Since there are 99 commodity codes, some of which only small numbers of goods crossed the US - Canada border, I'd like to group lower value amounts to make analysis easier.

       

      In other words, I'd like this table to break out the top 10-15 commodities crossing the border, and then group the rest in "other."

       

      Any easy way to do this in Tableau? I'm sure there is, but I don't even know where to start looking at this point.

       

      Thanks in advance for any help provided!

       

      Melissa

        • 1. Re: Tips for a newbie on grouping data
          lei.chen.0

          Hello Melissa,

           

          Melissa Fanucci wrote:

           

           

          In other words, I'd like this table to break out the top 10-15 commodities crossing the border, and then group the rest in "other."

           

          I'd like to confirm about the above requirement,

           

          1) how do you define the top 10-15 commodities crossing the border? sum of all exports and imports?

          2) what's the time range of the top 10-15? top 10-15 for from 2010 to 2015?

           

           

          Regards.

          • 2. Re: Tips for a newbie on grouping data
            Melissa Fanucci

            Hi Lei, thanks for responding. I'd like to separate out exports and imports because they will be different by direction.

             

            Time range, I'd like to see what the top 10-15 exports/imports are by year to compare year over year changes in commodity. So ideally...

             

            Top ten exports for 2010,2011,2012,2013,2014,2015

            Top ten imports for 2010,2011,2012,2013,2014,2015

             

            Thank you so much for any assistance you can provide!

             

            Melissa

            • 3. Re: Tips for a newbie on grouping data
              Nicholas Hura

              Hi Melissa,

               

              To find the Top N (in your case 10) there are a few steps:

               

              1. CTRL click on Year and Commodity Description and select Combine Field

              2. Change Year to discrete and drag out Year and the newly combined field Commodity Description & Year onto the Rows shelf

              3. Drag Sum(Exports by Truck, or whatever field you would like) onto the Columns shelf

              4. Select the dropdown for the Commodity Description & Year field and select Sort

              5. Select Sort By then your Field will be Exports by Truck (or whatever field you would like) and use Sum as the aggregation

              6. Create a new calculated field called Index and the formula is simply: Index()

              7. Convert Index to Discrete and drag it in between Year and Commodity Description & Year on the Rows shelf

              8. Select the Index dropdown and go to Compute Using and select Pane(down)

              9. Hold CTRL and drag the Index pill to Filters

              10. Change the Index filter to Continuous and select 1-10

              11. You are good to go! If you run into issues or it doesn't look right, make sure to check both the Index pill in Rows and Filters and that they are Computing Using: Pane(down)

               

              From there you can right click on the Commodity Description and create groups for each year based on the Top 10 you have displayed for each year.

               

              Hopefully this helps!

               

              Nick

              1 of 1 people found this helpful
              • 4. Re: Tips for a newbie on grouping data
                Melissa Fanucci

                OMG GENIUS!!!!! Thank you so much! Of course while I was able to recreate it, I understood about 50% of what I just did. I guess I better do more reading on the differences between discrete and non-discrete values.

                 

                So is Index() specifically a calculation to do just this, e.g. rank items?

                 

                Thank you again. You just made my Tuesday much better.

                 

                Melissa

                • 5. Re: Tips for a newbie on grouping data
                  Nicholas Hura

                  Melissa,

                   

                  I'm glad I was able to help. Discrete (blue) and Continuous (green) pills are essential to using Tableau and understanding them will make your life 100 times easier. You'll

                   

                  The INDEX function will be your best friend when it comes to more complex ranking/sorting. Basically, this table calculation will give you a rank field for every row or pane you can see in your viz. The RANK function has additional functionality allowing for addressing ties and other groupings based on rank.

                   

                  Also, here is a very good site that contains almost everything you would want to know about Tableau from a former Zen Master: Data + Science

                   

                  Best of luck!

                   

                  Nick

                  1 of 1 people found this helpful
                  • 6. Re: Tips for a newbie on grouping data
                    Melissa Fanucci

                    Great resource, thanks!