5 Replies Latest reply on Feb 19, 2020 1:19 AM by Tim Beard

    Create column hierarchy to have multiple columns under same heading

    Pradnya Ambre

      I have multiple columns like June_Profit, June_Sales, July_Profit, July_Sales and so on...

       

      All these columns are measures having integer values. I want to create a common heading called "June" under which I would like to have June_Profit as "Profit" and June_Sales as "Sales" and similar for other months.

       

      How do I achieve this result?

       

      Please refer to the attached image to see the expected outcome.

        • 1. Re: Create column hierarchy to have multiple columns under same heading
          Tim Beard

          Hi Pradnya Ambre,

           

          My first question is why do you want that. It looks like you are trying to force Tableau to behave like Excel (or the Excel users are forcing you to get Tableau to behave like Excel ). Why not either just produce the table in Excel or generate a vizualisation that might provide more insight than text in a table, i.e. use Tableau to its strengths? A simple examples below.

           

           

          However, to create the kind of table you mention above, have a look at the proposed solution here: Create hierarchy of calculated measures? If you open sheet 2 in this solution and move Letters up to Columns, you get the following, which seems to reflect what you're looking for:

           

           

          Tim

          • 2. Re: Create column hierarchy to have multiple columns under same heading
            Pradnya Ambre

            Hi Tim Beard,

             

            Thanks for sharing this. But, in this example, it looks like you already have Letters and Numbers defined in the dataset. What I want to do is a little different. I want to add the main heading manually as a text. For me, the main heading is not a part of the dataset. I am not sure if this is doable in Tableau. Please refer to the image attached.

             

            Thanks!

             

            P.S. I want to do this for better readability in the dashboard since I have too many records and columns in the actual table and Tableau because it gives me more functionalities than excel for the amount of data I have.

            • 3. Re: Create column hierarchy to have multiple columns under same heading
              Tim Beard

              Hi Pradnya Ambre,

               

              A couple of thoughts, ideas, ruminations and ramblings...

               

              You can easily add further elements such as titles (graphic, text or otherwise) by building it all together in a dashboard. Look at this week's WorkoutWednesday for a nice example of the kinds of thing you can do by placing elements together precisely in a dashboard.

               

              It's not what you want here, but if you have headers based on field names that don't look nice in a workbook, you can use aliases for them to display better text in headers.

               

              In a single worksheet (as opposed to workbook), you can only use data that is in the data set. However, you can introduce "mini data sets" for specific purposes. For example, I wanted to control whether a timeline was presented on a weekly, monthly, quarterly or yearly basis. For this I needed some buttons linked to a parameter:

              The dataset I needed for this was:

               

              Date PartButton
              weekW
              monthM
              quarterQ
              yearY

               

              To get this into Tableau, just highlight and copy the cells in excel, go to the data menu in Tableau and select paste. Then you can use it like any other data source.

               

              Having said all that, The best bet would be to build it up in a workbook. For me, a worksheet is never the finished item, just a component for the workbook. Using a worksheet alone is fine for testing, etc, but I'd never think of it as being "customer ready" as there are always other elements, whether headings, help buttons, graphical / branding elements such as logos, etc.that I need to incorporate.

               

              You could maybe build up the headings as you would like them in one worksheet, then align this with the data from the first worksheet into a workbook. This way, the headings remain dynamic, and would, for example automatically include 2020 when it comes along in a couple of weeks.

               

              Tim

              • 4. Re: Create column hierarchy to have multiple columns under same heading
                Mahak Bhatia

                Hi Pradnya,

                U can create a calculated field with all your months name and drag that to columns name. For measures names (sales/profit) u can edit alias.

                If it solves your issue please mark it as Correct.

                1 of 1 people found this helpful
                • 5. Re: Create column hierarchy to have multiple columns under same heading
                  Tim Beard

                  Hi pradnya sawarekar,

                   

                  I was pondering this again on the bus this morning and I think I have found a really easy solution:

                   

                  The key is "abusing" the dimension hierarchy function.

                   

                  1. Use the dropdown on the order date field to create a custom date with a year/month format:

                  2. Duplicate any further Dimension fields you want to have under the month (e.g. Category above)

                  3. Drag the category field over the new date field to create a date/category hierarchy

                  4. Drag Measure Names to the filters and select the measures you need, in this case Profit and sales

                  5. Drag the new order date field to columns and hit the plus button to expand the next level of the hierarchy, namely the new Category field

                  6. Drag Measure Names to columns

                  7. Drag Measure Values to Text

                  8. Optionally, drag Region (or any other dimension) to Rows

                  9. Bob's your uncle!Advanced editor and add an attachment:

                   

                  Example attached

                   

                  Tim

                  2 of 2 people found this helpful