Dynamic Reference lines in Tableau can be used to add some great context to the visualization that might otherwise be missed. Using Superstore Data, I want to view all historic Monthly Sales values across the US. I also want to be able to select a month and view the Total sales amounts for all months prior to and after the Month Value selected by the user, colouring before and after values separately in the process. So how do we go about this using set actions and parameter actions?

Set Actions
First of all, let’s create a custom date for Months. To do this, right click on the date field (in this case, Order Date), select Create, then custom date. Name it ‘Month’ and in the detail dropdown we will select Months and choose date value to make it continuous (see configuration below). Put the created Month field on columns, and Sales in the rows to get our view of sales over time.

Now create the set. Right click on your new Month field, select create and then Create Set. Name it ‘Month Set’ and for now just select one of your months which are not at the beginning or end of the data. Doing this allows us to see the dynamic reference line working later when we set it up.

We want the reference line we create to be dynamic, and set according to the set value so let’s create a calculated field and name it ‘Month Selected from Set’ and put the formula:
{ MIN(IF [Month Set] THEN [Month] END) }.
Drag this field to the detail shelf so we can use it in our reference line (the field must be continuous not discrete, so change if necessary).

Go to the Analytics page on the left and drag in a reference line to the Table for the Month field. You may be wondering why we needed to make the calculation for the ‘Month Selected from Set’ an LOD (Level of Detail) calculation. If we did not make this Calculation an LOD, the following graph would show. It’s difficult to notice, but the value of December 2018 I selected in my Month Set appears as a dot on the reference line and is not connected within my line graph.

Wrapping it in an LOD ensures that we see the following (notice the difference in the line graph around the reference line):

Now to set up the set action so that for any selected point on the line, the reference line will change according to our selection. Go to the worksheet tab and select actions, Add Action and Change Set Values. Name the action. Ensure the source sheets is just the current sheet and we want to run the action on Select. The Data Source should be pre-populated, but we must populate the Set dropdown with our Month Set. Running the Action will Assign values to set and clearing the selection will keep set values as we don’t want the reference line to move once we click away (see image below for set up).

Going back to the chart, if you select another point on your line, your reference line will change. In my case I have selected December 2019.

Now for the fun part, let’s colour! We will create a Boolean field that will colour below the reference line for true and above the reference line as the false values.

Create a calculated field and call it ‘Before Month Set’ and input the formula:
IF [Order Date] < [Month Selected from Set] THEN 'Before'
ELSE 'After'
END

This is saying if the Order Date is less than the selected month on the line graph, call the values ‘Before’, otherwise call them ‘After’. Drag this field to the colour and there you have it, your dynamic reference line with colours. This does however create a break in the line where the reference line is. This can be fixed by setting the Before Month Set field on the colour shelf to an Attribute by right clicking and selecting attribute.

I said in my requirements that I want to view the Total sales values before and after the selected month. Create a new sheet and drag the created ‘Before Month Set’ field to the columns and put your Sales on the text shelf. This is simply all you need to do, however I’m also going to add my ‘Before Month Set’ field to the colours so that it’s consistent with my graph. I’ll also right click and drag my ‘Month Selected from Set’ field to the detail shelf and select continuous months. This is so I can create a dynamic title for the worksheet too.

A quick view of these worksheets in a dashboard look something like this:

Hint: For the reference line to change dynamically in a dashboard, you need to add the action within the dashboard tab at the top since worksheet and Dashboard actions are added separately. The same configuration is required.

Parameter Action
Right click and drag Order Date to the columns, select continuous months, and put your sum of sales on the rows. We need to create a parameter that will allow the reference line we put in to our view to change dynamically. Select the dropdown in the data pane and choose create a parameter. Name your parameter ‘Month Parameter’, choose the data type to be Date and in your display format, choose the format ‘March 2001’. This format will be different if you’re using something other than Months.

Next let’s go to the analytics pane on the left hand side and drag in a reference line for the table on Month(Order Date). In the value dropdown, select Month Parameter – this will ensure that the reference line changes according to the parameter action we set up next. Label and Format the reference line as you wish. The configuration is shown below.

Create the parameter action. Go to the worksheet tab at the top of your screen, select actions then Add action and finally, Change Parameter. Name it as you wish and Run action on select. Select month parameter in the parameter target dropdown and your field should be Month(order date) or likewise. We don’t need any aggregation here and when clearing the selection, we want to keep the current value. See configuration below.

Now when we click on different values within the worksheet, the reference line will change accordingly, brilliant!

I also want to colour above and below the reference line. Create a calculated field and call it ‘Before Month Parameter’. Enter the formula:
IF [Order Date] < [Month Parameter] THEN 'Before'
ELSE 'After'
END

So if the order date is less than the date in our parameter, call the values ‘Before’ and if not, call them ‘After. This acts as a Boolean true or false. Drag this to the colour shelf.

You’ll notice two things that are a bit off when you select a point. The greying out of the values before the reference line (unless you click twice) and the breaking of the line graph at the reference line where the colours should meet.

There’s a simple trick to fix both of these.

To fix the line, right click on your field on the colour shelf and select attribute. This forces the lines to join back together.
To fix the greying out we can use a dummy field and add a filter action so that when you select a value, no greying will occur and you can view the colours before and after the reference line clearly with just one click.
Create a calculated field and call it ‘Dummy’. Within the field we just need to write some text so in my case I’m going to write “Hello” as seen below.

Drag the dummy field to the detail shelf. Now we need to set up our filter action in a very specific way. Go to the worksheet tab, Actions, Add Actions, Filter. Select only your current sheet as the source and target sheets and run action on select. In our case we want clearing the selection to show all values. In the target filters section, select the Selected Fields option and Add a Filter. A pop up box will appear. This is where we set the source field to be our Dummy. The target field has got to be a field that we are not using within our worksheet otherwise something will be filtered and our view will mess up, so I’m going to choose ship mode.

Now you are ready to go! Click OK on the open dialogue boxes and play around with your graph to see the created actions in action. Our final view looks like this:

Let’s also add sales values for before and after the reference line. Drag ‘Before Month Parameter’ to the columns and also to the colour shelf and drag sum(sales) to the text shelf.

Dragging our two sheets into a dashboard, we can create something like this, with a dynamic title added.

Thank you for reading, I hope this was helpful!