In this blog, I’ll be teaching you how to colour code a slope chart depending on whether there is an increase or a decrease. As an example, I'll be creating a slope graph comparing 2019 and 2020 sales, coloured depending on whether there is an increase or decrease from 2019.
How to create a basic Slope Chart:
- Put your discrete dimension onto columns and filters, filter down to the two members you want to compare. I'm using the years 2019 and 2020.
- Put your continuous measure i.e. sales on rows and change the mark type to line.
- Put your second discrete dimension onto detail to create a line for each member i.e. state.
Method 1: Using a Table Calculation
- Start by ctrl + dragging the Sum(Sales) from rows onto colour.
- Right click on Sum(Sales) in the colour shelf and add a table calculation
- Choose difference from, calculate using specific dimensions. Untick state only, essentially we're asking Tableau to calculate the difference in sales from the previous year, for each state.
- Save this Difference from calculation by Ctrl + Dragging the SUM(Sales) with the table calculation applied onto the data pane, I named mine ‘Difference Colour’
- The lines are all grey on the 2019 side as they have resulted in a NULL (Tableau is trying to compare 2019 to the previous year i.e. 2018, which we already filtered out at the start). The true difference we want is displayed on the 2020 mark.
- If our chart looked like a table, it would look like this:
- As we only want two colours rather than a gradient i.e. one for increase, and another for decrease, we need to change the table calculation we created into a boolean field.
- Edit the Difference Colour calculation, in here we add a > 0 onto the end of it to turn it into a True or False i.e. is the difference from the previous mark, more than 0?
- Note: if at any point the colour calculation breaks (turns red) whilst we alter it, just drag it off the marks card and drag the Difference Colour field back onto the marks card from the data pane.
- The odd gradient persists due to our NULLs issue we saw earlier, when asking whether a NULL is > 0, this also returns a null, resulting in a NULL blue colour for 2019. If the chart was a table it would look like this. Therefore, we need the true/false to copy across both years so that each line is a solid colour.
- To copy the value across, wrap the difference calculation in another LOOKUP function. What this essentially does is takes the above table, looks for the last value in each row, and fills it in for everything else. Our last value in each row is the 2020 true/false which will be copied across to our 2019 value.
- After that tweaking, your difference calculation is complete and you can choose a nice colour palette:
Method 2: Using a LOD calculation
- To really understand how the LOD works, I'll build out the calculations in a table first. As before, our slope graph is essentially this in table form:
Why we need an LOD:
- We can't isolate each year with a simple IF statement i.e. (IF YEAR([Order Date]) = 2019 then [Sales] END) as this creates the gaps that we saw before. Due to these gaps, it's impossible to perform a difference calculation, because on a row level, 2020 would be compared with a NULL.
Isolate each year's sales using calculated fields:
- These calculations read as: if the year is 2019, then turn that True into a 1 and times it by Sales, return this value for each state regardless of what year we're in.
- If the year isn’t 2019, the INT() would turn the False into a 0, which returns 0 overall as [Sales]*0 is nothing.
- This copies across the 2019 Sales value across the years, and does the same for the 2020 Sales too, we can now do a difference calculation.
- This results in a T/F i.e. two colours
- Same as before, build your slope graph and this time put your new 2020 > 2019 Sales? calculation onto colour.
As a bonus, whenever working with aggregates in Tableau the resulting colours can't be rearranged i.e. you can't move a certain colour to the front. We can disaggregate our difference calculation by removing the SUM() around our sales.
- Now in the colour card, you can now drag and drop True/False above/below each other depending on which one you want to focus on.