For this week’s challenge, Kyle aimed to solve a problem that he discussed with a #TC24 attendee while waiting in line for a session. How to show solid (categorical) and graduating (sequential) colours within the same chart.

For this we’re going to create 2 sheets, one displaying Sales and one for Profit Ratio.

Data Preparation

As the requirements referenced the use of the Manufacturer field, I connected to the Superstore Sales.tds file I had stored locally, rather than the .xls file, as Manufacturer doesn’t exist in the xls file.

Once connected, I also added a data source filter where Sub-Category = Storage (right click on data source > edit data source filter).

Building the Sales bar chart

Add Manufacturer to Rows and Sales to Columns and sort descending. Format Sales to be $ with 0 dp, and show mark labels.

Create a new field

Colour – Sales Bracket

IF SUM([Sales]) > 15000 THEN ‘>$15k’
ELSEIF SUM(Sales) >= 4000 THEN ‘$4k-$15k’
ELSE ‘<$4k’
END

Then create another field

Colour – Sales Range

IF SUM([Sales])<4000 THEN SUM([Sales])*-1 END

By default, this will be a continuous field as it returns a numeric value. But when having multiple fields on the Colour shelf, which is what we’re going to do, the fields need to be discrete. So convert Colour – Sales Range to discrete (right click on field).

You also might be wondering why we’re multiplying the value by -1. This is to ensure the values when listed in the colour legend are sorted in the way we want. This will become clearer shortly.

Add Colour – Sales Range to the Detail shelf, then click on the icon to the left of the pill, and select the Colour icon, to add this pill to the Colour shelf along with the Colour – Sales Bracket field.

Re-order the pills so Colour – Sales Range is listed first.

Your viz will look something like this

To change the colours without having to go through each legend option individually, edit the colour legend and choose a sequential colour palette that suits. In my case I used a custom one I had installed, but you could just opt for the Red-Gold that should be installed by default.

Click the Assign Palette button, and Tableau will automatically assign the colours in the graduated sequence, which is why the ordering of the entries matters. The colour legend lists the entries in ascending order, and as we want to display the values in descending order, multiplying by -1 reverses the order for the colour legend.

Manually set colours for the NULL, >$15k and NULL, $4k-$15k options. Add a pale grey border around the marks (via the Colour shelf). Then adjust the tooltip, remove the Manufacturer column heading label, hide the axis and remove all gridlines, zero lines, axis rulers, row/column dividers, and update the title.

Note – I chose to re-order how the two colour pills were listed on the Colour shelf so that there was a more noticeable difference between the colour chosen for the $4k-$15k range vs the first entry for the <$4k range.

Building the Profit Ratio bar chart

For this you will need similar fields

Colour – PR Bracket

IF [Profit Ratio] > 0.15 THEN ‘>15%’
ELSEIF [Profit Ratio] >= 0 THEN ‘0-15%’
ELSE ‘<0%’
END

and

Colour – PR Range

IF [Profit Ratio]<0 THEN [Profit Ratio]*-1 END

and Profit Ratio should be formatted to % with 1 dp.

You then just need to go through similar steps to that described above.

Building the dashboard

I used a horizontal container to position the two sheets in, side-by side. I set the outer padding of each sheet to 0 and inner padding to 5 and set to fit entire view. Between the sheets I added a blank object which I set to have outer padding of 0. I set the background colour of this blank object to a mid grey, then set the width to be 2, which produces the thin divider line.

To enable the highlighting between the sheets ‘on click’ I simply selected all fields from the highlight menu button.

A relatively short blog this week, but a great concept that’s worth knowing. My published viz is here.

Happy vizzin’!

Donna