Feb
8
2018

Side-by-Side Bar Chart combined with Line Chart

I was checking #Workoutwednesday challenge the other night and came across the following challenge.

 

So the challenge  is to replicate Jeff’s dashboard (image above ). In this challenge , Andy asked us to create Side-by-Side Bar Chart combined with Line Chart along with axis labeling to display the months rather than numbers as Jeffrey has done.

  • Dashboard size is 850*650
  • Quantity for the East and West regions should display as side-by-side bars
  • Sales for the East and West regions should be displayed as lines
  • Andy has filtered the view to 2015 only
  • Match the colours and tooltips
  • Match the axis labels (numeric months) or as an extra challenge Jeffrey’s labels (abbreviated months)

I recently tried something similar in my Project and I was pretty happy with my end result. When I saw a similar problem, I thought of replicating the same. Hope this helps you as well.

Step 1:

The side-by-side bar chart is just like the stacked bar chart except we’ve un-stacked them and put the bars side by side along the horizontal axis. This is an extension of the Blended axes chart. Let us follow the steps in the following recipe to quickly create a Side by Side Bar chart.

Now as per above challenge we need to show “Side by side bar chart combined with the line chart”. Order Quantity for the East and West regions should display as side-by-side bars and Sales amount for the East and West regions should be displayed as lines.

Many of you are thinking that this can be easily achieved by using the Dual Axis. If you tried, you would have come to know that you will end up creating Stacked Bar chart in one axis and Line Chart in another axis.

 

Step 2:

To achieve the side by side bar chart along with the Line chart, I will be using Custom SQL.

You can use custom SQL to union your data across tables, recast fields to perform cross-database joins, restructure or reduce the size of your data for analysis, etc.

Note: For Excel and text file data sources, this option is available only in workbooks that were created before Tableau Desktop 8.2 or when using Tableau Desktop on Windows with the legacy connection. To connect to Excel or text files using the legacy connection, connect to the file, and in the Open dialog box, click the Open drop-down menu, and then select Open with Legacy Connection.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SELECT

[Orders$].[Region] AS [Region],

[Orders$].[Order Date] AS [Order Date],

[Orders$].[Quantity] AS [MeasureValue],

‘Quantity – East’ AS  MeasureName,

‘Bar’ AS MarkType

FROM [Orders$]

Where [Orders$].[Region]= “East”

UNION

SELECT

[Orders$].[Region] AS [Region],

[Orders$].[Order Date] AS [Order Date],

[Orders$].[Quantity] AS [MeasureValue],

‘Quantity – West’ AS  MeasureName,

‘Bar’ AS MarkType

 

FROM [Orders$]

Where [Orders$].[Region]= “West”

UNION

SELECT

[Orders$].[Region] AS [Region],

[Orders$].[Order Date] AS [Order Date],

[Orders$].[Sales] AS [MeasureValue],

‘Sales’ AS  MeasureName,

‘Line’ AS MarkType

FROM [Orders$]

Where [Orders$].[Region]= “East”

UNION

SELECT

[Orders$].[Region] AS [Region],

[Orders$].[Order Date] AS [Order Date],

[Orders$].[Sales] AS [MeasureValue],

‘Sales’ AS  MeasureName,

‘Line’ AS MarkType

FROM [Orders$]

Where [Orders$].[Region]= “West”

 

Step 3:

Let’s create two calculated fields which gives us “Quantity” for Bar chart and “Sales” for Line chart.

 

 

 

 

 

 

 

Step 4:

Now we have Pivot field ( MeasureName and MeasureValue), Sales and Quantity. We need to create a Date Field (Date axis) labelling to display the months.

 

Step 5:

Once our calculated field is ready, just drag “Date Axis” on column Shelf, “Bar Chart” and “Line Chart” on Row shelf, “MeasureName” on the color shelf.


 

Now don’t forget to check your tooltips. Then add this worksheet to the dashboard and publish it to the Tableau Public. Do share a copy of work and Don’t forget to tag Andy and Jeffery on Twitter.

 

Alternative Approach : 

We need not require  Custom SQL anymore for developing the side by side bar chart combined with Line chart.

Special thanks to Jeffrey Shaffer , Andy Kriebel and Naveen Bandala for providing me the alternate solution.

Approach 1 :

Jeffrey suggested an alternate calculated Field for order date placement.

 

CASE [Region]

WHEN ‘North’ THEN DATETRUNC(‘month’,[Order Date])+ 0
WHEN ‘South’ THEN DATETRUNC(‘month’,[Order Date])+ 1
WHEN ‘East’ THEN DATETRUNC(‘month’,[Order Date])+ 2
WHEN ‘West’ THEN DATETRUNC(‘month’,[Order Date])+ 10

END

Approach 2 :

Coach Andy created another calculated  Field which not only aligned the side by side bar accurately but also keeps the month’s Label at the Center of the Bar chart.

Note *: Make Sure your Stack Marks are off ( Analysis – Stack Marks – Off)

 

 

 

 

 

 

IF [Region]=’East’ THEN -10
ELSEIF [Region]=’West’ THEN 10 END

 

Drag this Calculated field on the Size Shelf and change it to the Median.Please refer the below image.

 

Approach 3:

Naveen has suggested another way to achieve the same end result.He is using Index() for placing the Marks.

 

 

 

 

 

 

 

 

 

 

IF INDEX() = 1
THEN
ATTR(DATETRUNC(‘month’,[Order Date]))
ELSEIF
INDEX() = 2
THEN
ATTR(DATETRUNC(‘month’,[Order Date])+10)
END

Note *:

1) Make Sure Month(order date) should be in detail shelf and Region on Color marks.

2) As we are using Table calculation – Index, don’t forget to compute using Months and region.

 

Related Posts

About the Author: Rajeev Pandey

I am a multidisciplinary designer working in data visualization, interaction design and innovation. Expertise in developing Tableau, Web focus based visualization and reporting applications. I have a passion for analyzing, dissecting, and manipulating data sets as well as, building beautiful dashboard. Naturally talented in communicating between technology and business needs. Diverse and experienced in plenty of different domains .I am quick learner who can absorb new ideas and can communicate clearly and effectively.I love creativity and enjoy experimenting with various technologies.

Leave a comment

You must be logged into post a comment.