11 Replies Latest reply on May 3, 2018 5:26 AM by Yuriy Fal

    YoY Growth Charts for Dynamic Groups

    Sarang Parthasarathy

      Hello,

       

      I use an Excel file supplied as input from business team every month containing fiscal year data.

      Using data from this file, I'm trying to plot a combination chart (screenshot attached) in Tableau with a dataset containing country-wise sales information (and region mapping).

       

      I'm looking to design a Annual YoY growth rate comparison chart between two groups. These groups are dynamically defined - either between two Countries or Groups of countries, two regions or a region and Country (Group of countries). Therefore, there will always be two bars for each year (Fiscal Year or Calendar Year) and each bar will be the average of its group.

       

      If User selects a country (or a set of countries) from Group 1, then they can't select any region within Group 1. This is to avoid calculating the average among countries and regions. In the same way, if user selects a region (or a set of regions) from Group 1, then they cannot select any country from Group 1. Same approach is applicable for Group 2.

       

      I understand that I need to play around with the functionality of Parameters, Sets and Groups in Tableau. I need some help to build the above logic for designing the charts.

      Can someone explain how to implement the logic ? I use Tableau Desktop version 10.5.

        • 1. Re: YoY Growth Charts for Dynamic Groups
          Yuriy Fal

          Hi Sarang,

           

          Workbook, please.

           

          Yours,

          Yuri

          • 2. Re: YoY Growth Charts for Dynamic Groups
            Sarang Parthasarathy

            Hi Yuri,

             

            Find enclosed the Sample data used for the charts and the details of the visualizations that I'm trying to build using Tableau.

            I've mocked up the data for 2 manufacturers and a few products, there are a lot more manufacturers and products available in the original data.

             

            I've provided details of the visualizations that I'm trying to build in Tableau and as mentioned earlier I use version 10.5 and this data is stored in an Excel 2010 workbook.

             

            I built the chart in requirement #1 using Parameters, but unable to share the workbook for data confidentiality reasons.

            I would leave it to you to map the Country column in Sales worksheet (to the corresponding region of the globe).

             

            I'm looking to have multi-select filter like options to choose Countries, Manufacturers and Regions used in the charts that I'm looking to design, but using parameters does not fulfill my needs as they allow only single selection.

             

            Can you or someone try building the charts with the sample data and help ?

             

             

            Regards

            Sarang

            • 3. Re: YoY Growth Charts for Dynamic Groups
              Yuriy Fal

              Hi Sarang,

               

              Something like this?

              Please find the attached.

               

              Yours,

              Yuri

              1 of 1 people found this helpful
              • 4. Re: YoY Growth Charts for Dynamic Groups
                Sarang Parthasarathy

                Awesome, Yuri. Thanks a ton.

                This approach works, but I may need some logic changes to calculate the Country / Region (Flag).

                I would leave it to you to map the Country column in Sales worksheet (to the corresponding region of the globe).

                When I mentioned this statement in my previous post, I wasn't clear with my requirement. I missed including a Country and Region mapping worksheet in my sample data file. I've attached the updated worksheet.

                 

                There's an additional worksheet that is used for mapping the Region against the Country dimension in the Sales worksheet. I tried a Full Outer join (see attached screenshot) to combine the Country-Region worksheet to the dataset generated by the Union query and this approach works good., but with limited success

                 

                How can the Country / Region (Flag) be redefined considering the below points ?

                a. To include countries titled "Rest of" and "Total" in the Group 1 / Group 2 filter when Group 1 & Group 2 Parameters are Country

                b. To compare YoY Sales Growth across Region dimension, i.e. when Group 1 & Group 2 Parameters are Region.

                c. How to calculate YoY Sales Growth of one Country or a group of countries against a region or group of regions ?

                 

                 

                Regards

                Sarang

                • 5. Re: YoY Growth Charts for Dynamic Groups
                  Yuriy Fal

                  Hi Sarang,

                   

                  This would be even simpler.

                  Just refer the [Region] dimension directly

                  in both [Group N : Region] calculations.

                   

                  Please find the attached w/mod.

                   

                  Yours,

                  Yuri

                  1 of 1 people found this helpful
                  • 6. Re: YoY Growth Charts for Dynamic Groups
                    Sarang Parthasarathy

                    Thanks a billion, Yuri.

                     

                    One more thing that I wanted Tableau to take care (in the 1st chart) is Dynamic grouping on the Country Dimension.

                     

                    Consider this example,

                    Let's say there are 10 countries in Africa and user selects Kenya & (Always Select) under Group 1 and Ethiopia, Nigeria and Zimbabwe & (Always Select) under Group 2. Is it possible to group the rest of countries under African region (CAF) as Rest of Africa dynamically based on user selection ? Would like to know if this grouping can be done using Sets or if countries can be grouped dynamically.

                     

                    For the other chart (req. #2), should I design the chart with the same logic as you've done it in req. #1 ?

                    Correct me if I'm wrong anywhere on the below statement.

                     

                    For the 2nd chart, Group 1 and Group 2 will use the Manufacturer dimension instead of Country and Group 3 would be Region.

                    So just need to define one more group and use it in the YF: Group Filter field and apply it on the chart.

                     

                     

                    Regards

                    Sarang

                    • 7. Re: YoY Growth Charts for Dynamic Groups
                      Yuriy Fal

                      Hi Sarang,

                       

                      Frankly, I have no idea how the 'dynamic grouping' scenario

                      you've described could be implemented in Tableau.

                       

                      As of your req. #2, please come up with your own try --

                      i mean a workbook -- let's see if we could make it.

                       

                      Yours,

                      Yuri

                      • 8. Re: YoY Growth Charts for Dynamic Groups
                        Sarang Parthasarathy

                        As mentioned before, I won't be able to upload a workbook for data confidentiality reasons.

                         

                        For my table calculation for my 2nd chart, I use the below formula,

                        Semi-Annual Growth %.jpg

                        I try to cross-tab the data using table-calculations (see attached screenshot) to build a side-by-side bar chart.

                        I want the grand total growth % to be calculated for all Companies every time, irrespective of the number of companies filtered.

                         

                        When I apply the Company filter in Tableau on the cross tab, I see the grand total calculated only for the visible rows in the crosstab (as in screenshots 2 & 3).

                         

                        Paging Jonathan Drummey for help.

                        • 9. Re: YoY Growth Charts for Dynamic Groups
                          Yuriy Fal

                          Sarang,

                           

                          You can use my recent workbook

                          to modify and share.

                           

                          Yours,

                          Yuri

                          • 10. Re: YoY Growth Charts for Dynamic Groups
                            Sarang Parthasarathy

                            Hi Yuri,

                             

                            Glad to let you know that I was able to work it out and build the chart for my 2nd requirement.

                            I replicated the data source used for the other chart that you helped with and modified the Union query to design this chart.

                             

                            Thanks for your help. Really appreciate it !!

                             

                             

                            Regards

                            Sarang

                            • 11. Re: YoY Growth Charts for Dynamic Groups
                              Yuriy Fal

                              Sarang, i'm glad you got it working.

                               

                              Yours,

                              Yuri