Return to Data Tips Page

How to Color Entire Tableau Charts Based on Latest Performance

Tips

With the stock market being so volatile in recent weeks, we’ve been seeing lots of sparklines that show the daily performance for a symbol or index. The performance is often broken down by minute, but the entire chart is typically colored green if the overall daily performance is positive and red if the overall daily performance is negative.

While I wish the stories these charts had to tell were more positive, I really like the look of these and wanted to recreate them in Tableau. I’ve shown you in the past how to highlight the latest trends, but highlighting an entire chart based on the latest trends is trickier because of the default way data is aggregated in Tableau. This post shows you how to use level of detail expressions and get around Tableau’s order of operations to highlight entire charts based on a single value.

 

How to color entire sparklines based on the most current value

By the end of this post, you will be able to color each row (or column) in a chart based on whether the current performance was positive or negative.

This technique works with any continuous date part including Year, Quarter, Month, Week, Day, Hour, Minute, or Second. This means you could truly recreate the stock ticker sparklines described in the introduction, but so you can follow along using the Sample – Superstore dataset, we will start with these sparklines that look at Sales by Region by Month.

► Related video: How to Make Sparklines in Tableau

Now let’s say we want to color the sparkline for each region blue when the latest month over month change was positive and red when the latest month over month change was negative.

This tactic requires just three calculated fields. You can consolidate these three into just one, but I will create all three because (1) I want to show you each element individually, (2) the calculated fields will be easier to manage / troubleshoot, and (3) this allows you to use the individual calculations for other purposes.

The first calculated field isolates the latest month in the dataset. Again, this works with any date part, so simply replace ‘month’ in my calculation with the date part you are using. I’m also using the Sample – Superstore dataset, so the name of the date field is “Order Date”.

{MAX(DATETRUNC(‘month’,[Order Date]))}

The second calculated field isolates the second-to-last month in the dataset. This is accomplished with the DATEADD function by adding “-1” month to the Max Month. If you are using a date part other than month, you would once again replace ‘month’ with the date part you are using.

DATEADD(‘month’,-1,[Max Month])

The third and final calculated field determines if the latest month outperformed the second-to-last month. This will be a Boolean calculation that eventually colors the lines one color when the outcome is “true” and a second color when the outcome is “false”. My sparklines view is analyzing the Sales measure, so the formula would be:

{EXCLUDE [Order Date]: SUM(IF DATETRUNC(‘month’,[Order Date]) = [Max Month] THEN [Sales] END)}
>
{EXCLUDE [Order Date]: SUM(IF DATETRUNC(‘month’,[Order Date]) = [Max Month – 1] THEN [Sales] END)}

This one seems more complicated, but all it’s doing is looking at each date on the sparkline to determine if they match either the max month or the second-to-last month. Once those two are isolated, the formula determines whether sales for the max month is greater than the second-to-last month. The EXCLUDE level of detail expression at the beginning is what allows the color to be applied across every date, regardless of whether it’s the max month or second-to-last month.

► Related video: An Introduction to Tableau Level of Detail Expressions

I’m now ready to place the third calculated field on the Color Marks Card.

When the latest month outperformed the previous month, the entire line is colored blue; when the latest month underperformed the previous month, the entire line is colored red!

This is even dynamic when using a date filer, but there is one gotcha. Since our first calculated field (Max Month, in my case) uses a FIXED level of detail expression, it will always isolate the latest month in the dataset overall; not the latest month in the filtered range. In the current Sample – Superstore dataset, that means we’re stuck looking at December 2019’s performance versus November 2019’s performance.

However, because FIXED level of detail expressions happen after context filters in Tableau’s order of operations, we can add the date filter to context to have the latest month determined by the filtered range. To add a filter to context, right-click on it on the Filters Shelf and choose “Add to Context”.

► Related video: Tableau Filters and Order of Operations

Here’s how the view looks after adding a date filter to the Filters Shelf, adding the date filter to context, and selecting 1/1/2018 through 4/30/2019.

While I used sparklines as my example, this tactic works with other chart types. Here’s how the view looks after converting the sparklines to a dual-axis combination chart with a mark type of Line on one axis and a mark type of Area on the secondary axis!

Thanks for reading,
– Ryan