Tableau Tips & Tricks – Adding Multiple Dynamic ‘Reference Lines’ to a Continuous Line Chart
(User Level: Intermediate/Advanced)
Have you ever wanted to add multiple reference lines to a continuous line graph in your Tableau dashboards? This might seem like a perfect place to leverage the Reference Lines available in the Analytics Pane, but it’s not that straight forward. This post will walk you through the Reference Line options available through the Analytics Pane and how you can use other features in Tableau to create faux reference lines in case your particular viz can’t be created with those options.
The Analytics Pane
Analytics Pane is one of my favorite features in Tableau. It allows you to easily add reference lines, trend lines, forecasts, box plots, and cluster analyses to your visualizations with Tableau’s signature drag-and-drop interface.
As awesome as the Analytics Pane is, it has its limitations. One such limitation that I recently ran into was trying to add multiple date reference lines to a continuous line graph. I was working on a dashboard tracking the activity of customers who had enrolled in an annual promotion program. For each customer, I wanted to show the following:
Given the nature of the data, the graph for any given customer could have up to 3 reference lines (one per year). And that is where we run into issues with the Analytics Pane.
Figure 1 below shows my first attempt at this graph. First, I filtered the view to a customer who I knew had 3 distinct Enrollment Dates through prior data discovery. Then I created a continuous line graph of Spend by Month, using Activity Date as my date field. Then I added ATTR(Enrollment Date) to the Details Card and made it a continuous variable to make it available in the Reference Line menu. However, the Reference Line menu only has 3 options: Constant, Minimum, and Maximum.
Unfortunately, I couldn’t use the Constant option since I needed the results to update dynamically for each customer. The only options in the Reference Line menu that allow for these dynamic updates are Minimum and Maximum, which means that the Reference Line menu only let me add 2 of the 3 Reference Lines that I needed.
Luckily, Tableau has plenty of other awesome features that made it possible to create a workaround. Rather than using the Reference Line menu in the Analytics Pane, I was able to use dual axes, a constant calculated field, and some formatting options to make an unlimited number of dynamic ‘reference lines’ per customer.
Solution:
Step 1 – Creating a Dual Axis Chart
The first step in creating these new ‘reference lines’ is to move Enrollment Date from the Details Card to the Columns Shelf, as shown in Figure 2. Note that you’ll need to change Enrollment Date from an Attribute to a continuous Month by right-clicking on the Enrollment Date pill and updating the settings.
Now that there are two continuous date fields with the same granularity on the Columns Shelf, you can make this a dual axis chart and synchronize the axes, as shown in Figure 3.
Step 2 – Leveraging Constant Calculations
Next, you can create the field that you’ll use for your ‘reference line’ marks. Create a calculated field with a constant value of 10 and set the default aggregation to MIN. Add this new field to the Rows shelf, as shown in Figure 4.
Step 3 – Create Your ‘Reference Lines’
Now you’re in the home stretch. Click on the MONTH(Enrollment Date) Marks Card and change the shape from Automatic to Bar. Use the Size card to make the bars skinny so that they look like lines, just like in Figure 5.
Step 4 – Use Dual Axis One More Time
Next, right click on the MIN(Reference Line Marks) pill and choose the Dual Axis option.
Step 5 – Use Formatting to Hide All the Extra Stuff From the View
Finally, edit the right-hand y-axis and set it to a fixed range from 0 to 9. Then uncheck the ‘Show Header’ option for the right-hand y-axis and top x-axis and voila! You have multiple dynamic date reference lines for your continuous line graph.
Check out an interactive version of this graph below and feel free to download a copy of the packaged Tableau workbook to play around with this on your own. Be sure to subscribe and check back for more Tableau Tips & Tricks.
