Tableau Tip: How to Automatically Highlight the Latest Trends

First, I’m going to lay the foundation for the chart by making a sales by continuous month line graph from the Sample – Superstore dataset.

Next, I’m going to create a calculated field which will eventually draw a bar for the most recent month being visualized. The formula is:

IF ATTR(DATETRUNC(‘month’,[Order Date])) = WINDOW_MAX(ATTR(DATETRUNC(‘month’,[Order Date]))) THEN 1 END

It looks like we’re getting a little complex, but this formula simply causes Tableau to look at each month in the line graph and determine if it matches the latest month. When it matches, it shows the number 1 which will be used in the next step. Since this is a table calculation, I had to use the ATTR function to aggregate the dates.

Next, put the newly created Max Month calculated field on the Rows Shelf.

Change Max Month to continuous by clicking on its pill on the Rows Shelf and choosing “Continuous”.

Now that both measures on the Rows Shelf are continuous, they can be combined into a dual-axis combination chart. Yes; this is application #238 or so for this feature. Here are just a few of the ways I use dual-axis combo charts in Tableau.

After converting the view to a dual-axis chart, you can change the mark type of the measures independently of each other. For this example, I’ll change the mark type for Max Month to Bar.

At this point, the bar will always show up on the latest month in the view, even when the dates are filtered or if you’re using a relative date filter to always display data through the latest full month. Before we add color, there are a couple of formatting changes I’d like to do. First, I’ll fix the right axis from 0 to 1 by right-clicking on the axis, clicking “Edit Axis…”, and fixing the start and end. Also, I’ll make the bar evenly distributed around the latest point on the line graph by clicking its Size Marks Card, choosing “Manual”, and increasing the width of the bar. Lastly, I’ll increase the transparency of the bar by clicking the Color Marks Card and sliding the opacity slider to the left.

To color the bar, place the measure being evaluated on the Color Marks Card for the bar; in this case Sales. Then add a Quick Table Calculation that computes the month over month difference in sales.

This will work as is, but I usually change the color mapping so that anything positive is one color and anything negative is another color. You can do this by double-clicking the color legend, changing the steps to 2, and forcing the center to be 0 (under the advanced options). The last thing I do is hide the right axis for Max Month by right-clicking on the axis and unchecking “Show Header”.

Now whenever the most recent month had a negative month over month performance, a red highlighter will show up:

But even if the view is filtered to an older month or our data gets refreshed next month, the highlighter will automatically be displayed on the latest data point and colored based on month over month performance. Here’s an example when I filtered the dataset through August 2017.

Thanks for reading,
– Ryan