9 Replies Latest reply on Feb 6, 2019 4:39 AM by M Kalyan

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

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

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

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

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.'

Regards,

Monika.

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

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.

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

Regards,

Monika.

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

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

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

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

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

Hi Monika,

Hope you are doing good.

I have created the side by side chart with line but i'm facing the problem is in line chart selection, instead of line...dots are displaying on dashboard.

Could you please help me how could we get the line and i have attached the snapshot.

Please let me know if you need anything else from my end.

Appreciating the support..!

Regards,

M.Kalyan