11 Replies Latest reply on May 11, 2018 2:24 PM by Sarang Parthasarathy

    Dynamic Grouping for Rest of Countries in a Region

    Sarang Parthasarathy

      Hello Jon / Yuri,

       

      Came across some of your brain-teaser discussions and few forum thread discussions for plotting side-by-side bar charts in Tableau.

       

      I do have a similar requirement, but in my case, I need to group the Total Sales of the Rest of Countries in a region as a dynamic group instead of Grand Total. See screenshots in the attached document.

       

      Note that there are various countries listed under a region and may vary for different periods. Also, there is a existing grouped classification under the name "Rest of .... " which is different from the Rest of Region grouping as per my requirement.

       

      I use Tableau 10.5 and data source is an Excel 2010 Workbook.

        • 1. Re: Dynamic Grouping for Rest of Countries in a Region
          Peter Fakan

          Hi Sarang,

           

          Appreciate if you could upload a .twbx with an example of where you have gotten up to.

           

          At first impression, have you tried creating different groups (one of them being the "rest of"...) and then using a parameter to switch between them on your map ?

           

          HTH

           

          Peter

          • 2. Re: Dynamic Grouping for Rest of Countries in a Region
            Sarang Parthasarathy

            I want the country grouping to be done dynamically (based on filter selection).

             

            Here's the scenario:

            I've filters setup in my chart view which lists Regions (Africa, Asia, Europe, Latin America, Middle East, etc.)  and another dependent Country filter which list the bunch of countries corresponding to these regions.

             

            After users' select a region first, they would select a set of few countries in that region and the chart should show up the numbers for those (selected) countries. I want Tableau to group the rest of remaining countries from the filter (which ere not selected) and group them into one bar in the view. As per my example, the chart view shows a few selected countries in Africa and the remaining countries need to be grouped as Rest of Africa.

             

            Can this dynamic grouping (of Countries) be done through calculated fields in Tableau ?

            • 3. Re: Dynamic Grouping for Rest of Countries in a Region
              Sarang Parthasarathy

              I would not be able to upload a sample workbook for data confidentiality reasons.

               

              However, I was able to generate a small subset of the mocked up data used for building the visualization mentioned in my initial post.

              Enclosed the Excel workbook containing the mocked up data used for the charts described in my initial post.

               

              The dataset is combined into one single structure inside Tableau by using left join on the Product_Key & Sales_Key columns from the Product & Sales worksheets and mapping the Region column from the Country-Region worksheet.

               

              FY Period H1 is Jan-June wheras H2 is Jul-Dec for a Fiscal Year. For example, FY H1 2017 would be Jul-Dec 2016 and FY H2 2017 will be Jan-Jun 2017.

               

              As explained in my previous post, I use the Country and Region dimensions as filters in my view, with the Country filter listing relevant Countries based on the Region selected.

              Regions - Africa (CAF), South Asia (CSA), East Asia (CEA), Europe (CEU), Latin America (CLA), Middle East (CME) and Other.

               

              As shown in the attachment in my initial post, I want the Tableau chart to dynamically populate the Rest of Africa numbers, grouping all the countries left unselected from the Country filter into one. In other words, the chart view should show the list of selected countries in Africa and dynamically group the remaining unselected countries (from the filter) as Rest of Africa.

               

              I also have a few other doughnut charts built in the same workbook, where I show metrics distributed across selected countries in a region and would want to create one slice for the rest of the countries as one dynamic group.

              • 4. Re: Dynamic Grouping for Rest of Countries in a Region
                Sarang Parthasarathy

                At first impression, have you tried creating different groups (one of them being the "rest of"...) and then using a parameter to switch between them on your map ?

                Peter,

                 

                As you suggest, can you come up with the visualization that you have in your mind from the sample data I've provided ?

                • 5. Re: Dynamic Grouping for Rest of Countries in a Region
                  Peter Fakan

                  Hi Sarang,

                   

                  I'm still not 100% sure of your requirements, but am hoping to start with this superstore sample.

                   

                  I have setup 3 sheets, 1 is a map, the other 2 are state by sales, but one of these has an exclude filter on. The effect that this has is the 'selected' items are on the left side list, and the 'unselected' items are on the right hand side list. Is this what you are looking for as a dynamic populate the rest of region ?

                   

                   

                  HTH 2018.1 attached

                   

                  Peter

                  • 6. Re: Dynamic Grouping for Rest of Countries in a Region
                    Sarang Parthasarathy

                    Hi Peter,

                     

                    Taking the case of your sample Superstore example, I'm looking to dynamically group the 'unselected' list of states into a single group.

                     

                    As per my sample dataset (which I uploaded in my earlier post in this thread), I also have Region along with Country and use both these dimensions as multi-select drop-down filters in the below viz. that I'm looking to build.

                     

                     

                    I plotted the above side-by-side bar chart in Tableau (see 2nd screenshot below), but will need to dynamically group the list of countries left unselected as Rest of Africa in the view.

                    When Rest of Sub-Sahara Africa is selected in the filter, I also need to add up the sales volumes for the Countries that are left unselected in the Country filter including Rest of Sub-Sahara Africa sales volume.

                     

                    How can I dynamically group the countries left 'unselected' in the filter into a single group and add it to the view ?

                     

                     

                    Note :

                    1. The Country filter shows only dependent values based on the Region filter.

                    2. From my sample data, there is already data available under Rest of Sub-Sahara Africa.

                    3. The Calendar Year (calculated field) is added to the Color shelf on the Marks card.

                     

                     

                    Sarang

                    • 7. Re: Dynamic Grouping for Rest of Countries in a Region
                      Peter Fakan

                      Hi Sarang,

                       

                      I think I get what you are looking for. Illustrating the issue at the next layer up - you can see that I can dynamically select a bunch of cities (replace with countries in your example), but they all aggregate up to the same state (replace with continents in your example). The effect this will have is you will be presented with Africa/Africa instead of your selection and 'rest of Africa' on the other side.

                       

                       

                      To get around this issue, duplicate your datasource but in the second datasource change the title of the aggregate layer in your data to 'rest of Africa' and blend on the next level down. Then when you build your second sheet, building it off the second datasource so that the aggregate level says 'rest of Africa' instead of 'Africa'.

                       

                      HTH

                       

                      Peter

                      • 8. Re: Dynamic Grouping for Rest of Countries in a Region
                        Sarang Parthasarathy

                        To get around this issue, duplicate your datasource but in the second datasource change the title of the aggregate layer in your data to 'rest of Africa' and blend on the next level down. Then when you build your second sheet, building it off the second datasource so that the aggregate level says 'rest of Africa' instead of 'Africa'.

                        As per your example, you've 2 different text tables and show them adjacent to each other on dashboard based on selection on the Map.

                         

                        As per your statement (quoted above), I may then need to build another side-by-side bar chart showing volumes only for the rest of countries grouping (after duplicating the data source).

                        And then when displaying the viz. on the dashboard, I should fit them into one single container. Correct me if my understanding is wrong here.

                         

                        I also do have some doughnut charts built in my workbook, where I may need to show a pie slice for the sales volume (or) percent of total (or) YoY distribution (or) a table calculation.

                        How can I incorporate the grouping logic for such doughnut / pie charts ?

                         

                        Is there any other approach possible without duplicating the data source & blending the data ?

                         

                         

                        Sarang

                        • 9. Re: Dynamic Grouping for Rest of Countries in a Region
                          Sarang Parthasarathy

                          Is there any other way to group the Countries (as explained in my earliest post) without duplicating the data source ?

                          • 10. Re: Dynamic Grouping for Rest of Countries in a Region
                            Yuriy Fal

                            Hi Sarang,

                             

                            Your requirements could be fulfilled (well, almost)

                            by using a UNIONed datasource

                            (same as in your previous post),

                            with some conditions met --

                            such as filtering Countries

                            from the single Region only.

                             

                            Please find the attached w/mod.

                             

                            Yours,

                            Yuri

                            1 of 1 people found this helpful
                            • 11. Re: Dynamic Grouping for Rest of Countries in a Region
                              Sarang Parthasarathy

                              Awesome, Yuri.

                              You've hit the nail on the head. This is exactly what I was looking for.

                               

                              Thanks for the assistance.