9 Replies Latest reply on Oct 22, 2013 6:52 AM by Murali Govindu

    Creating Group Column above selected Columns.

    Murali Govindu

      Hi Again,

       

      I want to add a column above the current level of field data/ column, kindly read below scenario and let me know if you need any other clarification to achieve a solution.

       

      What I have:

       

      Dimensions

       

      Employer Name

      Employer Location

       

      Measures

       

      Salary in 1975 US$

      Salary in 1982 US$

      Salary in 1987 US$

      Salary Growth %

      Salary Forecast US$

      Hours in 1975

      Hours in 1982

      Hours in 1987

      Hourly Growth %

      Hours Forecast

       

      The dashboard displays:

                                                                                      Employer Name

                                                                                      Employer Location

      Employer Name | Salary in 1975 US$ | Salary in 1982 US$ | Salary in 1987 US$ | Hours in 1975 | Hours in 1982 | Hours in 1987

       

      I want to achieve without altering the source data as follows:

       

                                                                                      Employer Name

                                                                                      Employer Location

      Employer Name |                                         SALARY                                     |                                          HOURS

                                Salary in 1975 US$ | Salary in 1982 US$ | Salary in 1987 US$ | Hours in 1975 | Hours in 1982 | Hours in 1987

       

      I want to add the category name to all the Salary fields as shown above in blue text SALARY and Hour fields as HOURS as stated above.

       

      I tried Tableau features involving Grouping, Hierarchy, etc.. to bring such a level namely SALARY, HOURS, but couldn't achieve.  Can anyone through some light on this pl.?

       

      Thanks and regards,

      Murali.Govindu@gmail.com

        • 1. Re: Creating Group Column above selected Columns.
          Dan Huff

          This will not be possible on a data level without reshaping the data as you suspected.

           

          In Tableau 8, however, you could add a floating text box on a dashboard that would allow you to visually group these together in a slightly less dynamic way.


          Dan

          1 of 1 people found this helpful
          • 2. Re: Creating Group Column above selected Columns.
            Jim Wahl

            Hi Murali,

             

            If your main goal is a report, then Dan's floating text box would be my choice.

             

            You might also consider duplicating the data source and reshaping just the relevant columns using a custom SQL connection. Then, of course, your underlying data source doesn't need to change, but you get the benefit of reshaping, which might be significant here.

             

            Jim

            1 of 1 people found this helpful
            • 3. Re: Creating Group Column above selected Columns.
              Murali Govindu

              Thanks Dan.

               

              Thank you Jim, I hardly tweaked Custom SQL but will try now and hope success follows me.

               

              Best regards,

              • 4. Re: Re: Creating Group Column above selected Columns.
                Murali Govindu

                Attached find the packaged workbook.  I was trying to tweak the SQL to get a field and not sure due to inexperience.  Can anyone look at attachment and let me know how to bring the SALARY and HOURS columns above relevant fields like Salary 1975, Salary 1982, etc..)

                 

                Thanks in advance,

                 

                Best regards,

                murali.govindu@gmail.com

                • 5. Re: Re: Re: Creating Group Column above selected Columns.
                  Jim Wahl

                  Hi Murali,

                   

                  The goal of reshaping is to convert the data from wide to tall:

                  Wide Data: 4 rows x 8 columns

                  2013-10-18 18-53-36.png

                  Tall Data: 12 rows by 5 columns

                  2013-10-18 19-05-24.png

                  To reshape the data inside Tableau, you need to select the data connection > Edit Connection > Custom SQL and then do some cutting and pasting (there are other ways of doing this and a proper database may also have a built in function to unpivot). The key SQL for Excel / Access / CSV data sources is UNION ALL.

                   

                  SELECT 
                    ['DATA RESHAPING IN TABLEAU$'].[City] AS [City],
                    ['DATA RESHAPING IN TABLEAU$'].[Location] AS [Location],
                    1975 as [Year],
                    ['DATA RESHAPING IN TABLEAU$'].[Hours 1975] AS [Hours],
                    ['DATA RESHAPING IN TABLEAU$'].[Salary 1975] AS [Salary]
                  FROM ['DATA RESHAPING IN TABLEAU$']
                  UNION ALL
                  SELECT 
                    ['DATA RESHAPING IN TABLEAU$'].[City] AS [City],
                    ['DATA RESHAPING IN TABLEAU$'].[Location] AS [Location],
                    1982 as [Year],
                    ['DATA RESHAPING IN TABLEAU$'].[Hours 1982] AS [Hours],
                    ['DATA RESHAPING IN TABLEAU$'].[Salary 1982] AS [Salary]
                  FROM ['DATA RESHAPING IN TABLEAU$']
                  UNION ALL
                  SELECT 
                    ['DATA RESHAPING IN TABLEAU$'].[City] AS [City],
                    ['DATA RESHAPING IN TABLEAU$'].[Location] AS [Location],
                    1987 as [Year],
                    ['DATA RESHAPING IN TABLEAU$'].[Hours 1987] AS [Hours],
                    ['DATA RESHAPING IN TABLEAU$'].[Salary 1987] AS [Salary]
                  FROM ['DATA RESHAPING IN TABLEAU$']
                  

                   

                  Now that you have the reshaped data source. You can go from:

                  2013-10-18 19-10-32.png

                   

                  To this:

                  2013-10-18 19-11-23.png

                   

                  Jim

                  1 of 1 people found this helpful
                  • 6. Re: Creating Group Column above selected Columns.
                    Murali Govindu

                    Thanks Jim for your quick inputs.  Thanks again for the insight.  I will go though in detail now and get back if necessary.

                     

                    Regards,

                    • 7. Re: Creating Group Column above selected Columns.
                      Murali Govindu

                      Basing on your example, I worked on my SQL and could achieve the heading/ broad categories (like SALARY and HOURS) but not actual columns below them.  Pl. go through below, I am in need of a query writing advise here. How about using Grouping or sub-function to achieve this?

                       

                      Originally, I have this data, out of which I want to categorize the data (refer fig.1) on Revenues, Units, ASP and GM, however, I could achieve partially by categorizing the data through you good example, but could not manage the sub-categories/ fields below it (refer fig.2 below).

                      Fig.1.png

                      Fig.1


                      Fig.2


                      I tried as below, but it doesn't work.  its Union All, I balanced the script by bringing the GM related fields through there is no value.  I think Grouping and sub-function could help me, is there anyone to look at this once and let me know please?

                      SELECT

                      [EMDM_Focus_Product_Dsb].[DIM_Buyring_Locn_Key] AS [DIM_Buyring_Locn_Key],

                        [EMDM_Focus_Product_Dsb].[Region_Key] AS [Region_Key],

                        [EMDM_Focus_Product_Dsb].[Franchise_Key] AS [Franchise_Key],

                        [EMDM_Focus_Product_Dsb].[USER_ID] AS [USER_ID],

                        [EMDM_Focus_Product_Dsb].[BATCH_ID] AS [BATCH_ID],

                        [EMDM_Focus_Product_Dsb].[Master_Item_Key] AS [Master_Item_Key],

                      /*REVENUE_PYTD as [Previous YTD],

                      UNITS_PYTD as [Previous YTD],

                      ASP_PYTD as [Previous YTD],

                      GM_PYTD as [Previous YTD],*/

                      [EMDM_Focus_Product_Dsb].[REVENUE_PYTD] AS [REVENUE],

                        [EMDM_Focus_Product_Dsb].[UNITS_PYTD] AS [UNITS],

                        [EMDM_Focus_Product_Dsb].[ASP_PYTD] AS [ASP],

                        [EMDM_Focus_Product_Dsb].[GM_PYTD] AS [GROWTH]

                      FROM [dbo].[EMDM_Focus_Product_Dsb]

                      UNION ALL

                      SELECT

                      [EMDM_Focus_Product_Dsb].[DIM_Buyring_Locn_Key] AS [DIM_Buyring_Locn_Key],

                        [EMDM_Focus_Product_Dsb].[Region_Key] AS [Region_Key],

                        [EMDM_Focus_Product_Dsb].[Franchise_Key] AS [Franchise_Key],

                        [EMDM_Focus_Product_Dsb].[USER_ID] AS [USER_ID],

                        [EMDM_Focus_Product_Dsb].[BATCH_ID] AS [BATCH_ID],

                        [EMDM_Focus_Product_Dsb].[Master_Item_Key] AS [Master_Item_Key],

                      /*REVENUE_YTD as [YTD],

                      UNITS_YTD as [YTD],

                      ASP_YTD as [YTD],

                      GM_YTD as [YTD],*/

                      [EMDM_Focus_Product_Dsb].[REVENUE_YTD] AS [REVENUE],

                        [EMDM_Focus_Product_Dsb].[UNITS_YTD] AS [UNITS],

                        [EMDM_Focus_Product_Dsb].[ASP_YTD] AS [ASP],

                        [EMDM_Focus_Product_Dsb].[GM_YTD] AS [GROWTH]

                      FROM [dbo].[EMDM_Focus_Product_Dsb]

                      UNION ALL

                      SELECT

                      [EMDM_Focus_Product_Dsb].[DIM_Buyring_Locn_Key] AS [DIM_Buyring_Locn_Key],

                      [EMDM_Focus_Product_Dsb].[Region_Key] AS [Region_Key],

                      [EMDM_Focus_Product_Dsb].[Franchise_Key] AS [Franchise_Key],

                      [EMDM_Focus_Product_Dsb].[USER_ID] AS [USER_ID],

                      [EMDM_Focus_Product_Dsb].[BATCH_ID] AS [BATCH_ID],

                      [EMDM_Focus_Product_Dsb].[Master_Item_Key] AS [Master_Item_Key],

                      /*REV_GROWTH_PRT as [Percentage],

                      UNITS_GROWTH_PRT as [Percentage],

                      ASP_GROWTH_PRT as [Percentage],

                      GM_PYTD as [Previous YTD],*/

                      [EMDM_Focus_Product_Dsb].[REV_GROWTH_PRT] AS [REVENUE],

                      [EMDM_Focus_Product_Dsb].[UNITS_GROWTH_PRT] AS [UNITS],

                      [EMDM_Focus_Product_Dsb].[ASP_GROWTH_PRT] AS [ASP],

                      [EMDM_Focus_Product_Dsb].[GM_PYTD] AS [GROWTH]

                      FROM [dbo].[EMDM_Focus_Product_Dsb]

                      UNION ALL

                      SELECT

                      [EMDM_Focus_Product_Dsb].[DIM_Buyring_Locn_Key] AS [DIM_Buyring_Locn_Key],

                      [EMDM_Focus_Product_Dsb].[Region_Key] AS [Region_Key],

                      [EMDM_Focus_Product_Dsb].[Franchise_Key] AS [Franchise_Key],

                      [EMDM_Focus_Product_Dsb].[USER_ID] AS [USER_ID],

                      [EMDM_Focus_Product_Dsb].[BATCH_ID] AS [BATCH_ID],

                      [EMDM_Focus_Product_Dsb].[Master_Item_Key] AS [Master_Item_Key],

                      /*REVENUE_FORECAST as [FORECAST],

                      UNITS_FORECAST as [FORECAST],

                      ASP_FORECAST as [FORECAST],

                      GM_PYTD as [FORECAST],*/

                      [EMDM_Focus_Product_Dsb].[REVENUE_FORECAST] AS [REVENUE],

                      [EMDM_Focus_Product_Dsb].[UNITS_FORECAST] AS [UNITS],

                      [EMDM_Focus_Product_Dsb].[ASP_FORECAST] AS [ASP],

                      [EMDM_Focus_Product_Dsb].[GM_PYTD] AS [GROWTH]

                      FROM [dbo].[EMDM_Focus_Product_Dsb]

                      UNION ALL

                      SELECT

                      [EMDM_Focus_Product_Dsb].[DIM_Buyring_Locn_Key] AS [DIM_Buyring_Locn_Key],

                      [EMDM_Focus_Product_Dsb].[Region_Key] AS [Region_Key],

                      [EMDM_Focus_Product_Dsb].[Franchise_Key] AS [Franchise_Key],

                      [EMDM_Focus_Product_Dsb].[USER_ID] AS [USER_ID],

                      [EMDM_Focus_Product_Dsb].[BATCH_ID] AS [BATCH_ID],

                      [EMDM_Focus_Product_Dsb].[Master_Item_Key] AS [Master_Item_Key],

                      /*REVENUE_FCST_PRT as [Forecast %],

                      UNITS_FCST_PRT as [Forecast %],

                      ASP_FCST_PRT as [Forecast %],

                      GM_PYTD as [Forecast %],*/

                      [EMDM_Focus_Product_Dsb].[REVENUE_FCST_PRT] AS [REVENUE],

                      [EMDM_Focus_Product_Dsb].[UNITS_FCST_PRT] AS [UNITS],

                      [EMDM_Focus_Product_Dsb].[ASP_FCST_PRT] AS [ASP],

                      [EMDM_Focus_Product_Dsb].[GM_PYTD] AS [GROWTH]

                      FROM [dbo].[EMDM_Focus_Product_Dsb]

                      • 8. Re: Re: Creating Group Column above selected Columns.
                        Jim Wahl

                        Hi Murali,

                         

                        I just glanced at this quickly (so I may not be getting your goal), but it seems like you have a crosstab with four time periods (PYTD, YTD, PRT, FORECAST) for four measures (REVENUE, UNITS, ASP, GROWTH).

                         

                        Your SQL above looks fine, except that where you have the comment text. This should be just one line for the time period. Something like:

                         

                        SELECT
                          [EMDM_Focus_Product_Dsb].[DIM_Buyring_Locn_Key] AS [DIM_Buyring_Locn_Key],
                        ...
                          'PYTD' AS [Time Period]
                          [EMDM_Focus_Product_Dsb].[REVENUE_PYTD] AS [REVENUE],
                          [EMDM_Focus_Product_Dsb].[UNITS_PYTD] AS [UNITS],
                          [EMDM_Focus_Product_Dsb].[ASP_PYTD] AS [ASP],
                          [EMDM_Focus_Product_Dsb].[GM_PYTD] AS [GROWTH]
                        FROM [dbo].[EMDM_Focus_Product_Dsb]
                        UNION ALL
                        SELECT
                          [EMDM_Focus_Product_Dsb].[DIM_Buyring_Locn_Key] AS [DIM_Buyring_Locn_Key],
                        ...
                          'YTD' AS [Time Period]
                          [EMDM_Focus_Product_Dsb].[REVENUE_YTD] AS [REVENUE],
                          [EMDM_Focus_Product_Dsb].[UNITS_YTD] AS [UNITS],
                          [EMDM_Focus_Product_Dsb].[ASP_YTD] AS [ASP],
                          [EMDM_Focus_Product_Dsb].[GM_YTD] AS [GROWTH]
                        FROM [dbo].[EMDM_Focus_Product_Dsb]
                        UNION ALL
                        SELECT
                          [EMDM_Focus_Product_Dsb].[DIM_Buyring_Locn_Key] AS [DIM_Buyring_Locn_Key],
                        ...
                          'PRT'  AS [Time Period]
                          [EMDM_Focus_Product_Dsb].[REV_GROWTH_PRT] AS [REVENUE],
                          [EMDM_Focus_Product_Dsb].[UNITS_GROWTH_PRT] AS [UNITS],
                          [EMDM_Focus_Product_Dsb].[ASP_GROWTH_PRT] AS [ASP],
                          [EMDM_Focus_Product_Dsb].[GM_PYTD] AS [GROWTH]
                        FROM [dbo].[EMDM_Focus_Product_Dsb]
                        UNION ALL
                        SELECT
                          [EMDM_Focus_Product_Dsb].[DIM_Buyring_Locn_Key] AS [DIM_Buyring_Locn_Key],
                        ...
                          'FORECAST' AS [Time Period]
                          [EMDM_Focus_Product_Dsb].[REVENUE_FORECAST] AS [REVENUE],
                          [EMDM_Focus_Product_Dsb].[UNITS_FORECAST] AS [UNITS],
                          [EMDM_Focus_Product_Dsb].[ASP_FORECAST] AS [ASP],
                          [EMDM_Focus_Product_Dsb].[GM_PYTD] AS [GROWTH]
                        FROM [dbo].[EMDM_Focus_Product_Dsb]
                        

                         

                        Jim

                        • 9. Re: Re: Creating Group Column above selected Columns.
                          Murali Govindu

                          Thanks for the response Jim.  I will be looking at this shortly to consider in my solution.

                           

                          Best regards,