8 Replies Latest reply on May 15, 2018 12:21 PM by Nikhil Sahni

    How to create a combination graph (bar and line graph)0 which consists of 3 measures and one dimension

    Monika G


      Hi ,

      Can anyone tell me how to create combination graph (line graph with one measure and bar graph with 2 measures) as per the attached mockup chart.

       

      I'm unable to create the graph as shown in mockup attached.

       

      Please anyone can create the combination graph with sample attached so that it will be very helpful..

      Thank you so much in advance for help.

       

      Regards,

      Monika.

        • 1. Re: How to create a combination graph (bar and line graph)0 which consists of 3 measures and one dimension
          Tracy Rodgers

          Hi Monika,

           

          Jonathan Drummey's blog post found here should be able to help you get closer to the desired outcome:

           

          http://drawingwithnumbers.artisart.org/bars-and-lines/

           

          -Tracy

          • 2. Re: How to create a combination graph (bar and line graph)0 which consists of 3 measures and one dimension
            Matt Lutton

            Monika:

             

            First, I would suggest that you always post packaged workbooks (if at all possible) if you need help--Getting the Most From the Tableau Forums

             

            The folks around here are very helpful, but it is difficult to help without seeing what you've tried first.  I can tell you from experience that you will get the most help when you explain what you've already tried, and why you couldn't get the results you wanted.

             

            Can you explain your numbers in the Excel sheet?  You have two columns with "Actual Temp" and the "Actual Temp Hours" and "Planned Temp Hours" are the same values.  Am I missing something?

            • 3. Re: How to create a combination graph (bar and line graph)0 which consists of 3 measures and one dimension
              Monika G

              Hi Tracy and Matthew,

               

              I have gone through Jonathan Drummy's blog before..but didn't get the chart as per my requirement. Thats why  i posted here.

               

               

              In my excel data attached, Actual temp, Actual temp Hours, Planned temp Hours are different measures.

              Month and Week ending Date are two dimensions.

              As shown in the attachment in the previous post Combination chart.png is combination of Bar chart and Line chart using the data in the excel sample data.xls.

               

              As shown in the attached twbx file(combinataion chart.twbx), i have tried to combine one dimension Month, bar chart using 2 measures(Actual Temp Hours and Planned temp Hours) and line chart with Actual Temp measure. But in line chart selection, instead of line...dots are displaying on dashboard.

               

              Please cretae workbook for my requirement as shown in the image combination chart.png and send me the twbx file.'

               

              Thank you in advance.

              Regards,

              Monika.

              • 4. Re: Re: How to create a combination graph (bar and line graph)0 which consists of 3 measures and one dimension
                Jim Wahl

                Hi Monika,

                 

                The basic problem is that the columns shelf needs to be a continuous value in order to get a continuous line, but to get the bars side-by-side you've had to use the discrete measure names pill.

                 

                Fixing this requires

                • reshaping the data to create measure names / measure values columns in your data source. This is necessary, because Tableau doesn't allow you to use measure names in a calculated field, and
                • creating a continuous "bar position" date field that will put the bars side-by-side.

                 

                This is not difficult, but it's not exactly elegant or easy to maintain. Maybe you'd consider a different chart type, such as a bullet chart? Or even two charts, one with the bars and one with the line. Both, I believe, are easier for users to quickly analyze and avoid the manual work below.

                 

                It looks like you're using Tableau 7, so that's what I've used below. Tableau 8 would give you a little more flexibility on the formatting, but otherwise shouldn't make much difference.

                 

                Creating Your Combo Graph

                Here's how I created your desired chart--see attached workbook and png.

                 

                1) Add Actual Temp % column to Excel sheet.

                 

                2) Reshape the data in Excel using Tableau's Excel plugin.

                When reshaping select the cell in the top most date row (row 2 in my spreadsheet) and the first measure column (Actual Temp hours).

                 

                After reshaping you should have a "tall" rather than "wide" data set with four times as many rows and just two columns for the measure names and measure values. I called these new columns "Hour Category" and "Hour Amount".

                 

                3) Create Lines and Bars fields in Tableau

                Open this data source and create two new fields on for the bars and one for the lines.

                Bar ValuesIF [Hour Category] == 'Actual temp Hours' OR [Hour Category]  == 'Planned Temp hours' THEN [Hour Amount] END

                Line Values: IF [Hour Category] == 'Actual Temp %' THEN [Hour Amount] END

                 

                4) Create a proper date from Month, Year

                Since we need a continuous date value, these fields need to be combined into a Tableau date. I created a new field

                Mont (date format) =

                  DATE([Month]+" 1, "+STR([Year]))

                 

                5) Create bar position

                If you just add the bar values with a normal date field, they will either be stacked or overlaid (if the you have stacked marks disabled). Since you want them side-by-side, a small hack is required. We'll create new date field called bar position that creates a left and right bar based on the category. This was the key trick in Jonathan Drummey's tutorial.

                 

                Since you are displaying the values by month, we'll truncate the dates to month (2012-02-25 becomes 2012-02-01) and then subtract three days for the first category and add three days for the second. You may need to play with these values a bit to make it look right.

                Bar Position:

                  CASE [Hour Category]

                    WHEN "Actual temp Hours" THEN DATETRUNC('month',[Month (date format)])  - 3    // Subtract three days

                    WHEN "Planned Temp hours" THEN DATETRUNC('month',[Month (date format)]) + 3    // Add three days

                  ELSE

                    DATETRUNC('month',[Month (date format)])  // We want the line to be centered.

                  END

                 

                6) Building the view

                Add Bar Position to columns. Right-click to select exact date. And make sure it's a continuous value (green pill).

                 

                7) Cleanup

                X-axis date: I'd format the date on the x-axis to be a custom format mmm, yy.

                 

                Colors: I edited the colors to associate the blue bars with the left axis and the orange line with the right axis.

                 

                Line marks: From your previous post, I think you like the line marks. In Tableau 7, you can enable these by selecting show labels and then, assuming you don't want the text value, make the text transparent in the label font selection dialog.

                 

                Another View

                Again, if you wanted to do something without reshaping your data or creating several new fields AND that is closer to business data viz best practices, you have a couple of options.

                 

                A bar chart showing actual with gnatt lines showing the planned. Or a bullet chart---with Tableau's Show Me feature, just select the two measures and the date and click on the bullet chart icon.

                 

                See attached images. In my opinion they are easier and better. ...

                 

                Hope this helps.

                 

                Jim

                2 of 2 people found this helpful
                • 5. Re: Re: How to create a combination graph (bar and line graph)0 which consists of 3 measures and one dimension
                  Monika G

                  Thank yuo so much Jim. Your information is really very helpful.

                   

                  Regards,

                  Monika.

                  • 6. Re: How to create a combination graph (bar and line graph)0 which consists of 3 measures and one dimension
                    Jasmine McDonald

                    Your post related to the combination chart appears easy to accomplish; however, I don't think I'm laying out my data correctly. Can you provide some assistance on my dummy data?

                    • 7. Re: How to create a combination graph (bar and line graph)0 which consists of 3 measures and one dimension
                      Nikhil Sahni

                      Hi Tracy,

                      I don't understand the part " Setting up the Union Query" Customizing Grand Totals – Part 3 | Drawing with Numbers . I am not able to do it I am using Tableau 9.3. Could you please explain this to me?

                       

                      Regards

                      Nikhil

                      • 8. Re: Re: How to create a combination graph (bar and line graph)0 which consists of 3 measures and one dimension
                        Nikhil Sahni

                        Hi Jim,

                        I found your workbook 'Combination_chartv2.twbx" pretty useful, but I am not able to create the exact same view. I am attaching my workbook for your reference. Please guide me through this what am I doing wrong and what changes should I make to get the same result as yours. Here are some things that I want to show in my workbook.

                        1. Bars representing "Correctly Identified" and "Incorrectly Identified" should be placed side by side aggregated at the week level. Now in my workbook (I followed your approach) I want my view at the week level unlike yours (month level). The bars are too close and are overlapping with the bars of the next week. I want them side by side or less overlapped just like yours. Also I want to keep the same start date of the week to be shown when I hover on both the bars.

                         

                        2. I want to show a line chart in the same sheet representing the percentage of Correctly Identified aggregated at the week level. I made one calculated field with that name.

                         

                        I have made changes to the custom sql as well. If there are any changes that you think would help me create the view I want, please let me know.

                         

                        Regards

                        Nikhil