KNOWLEDGE BASE

Creating Dynamic Reference Lines


Product(s): Tableau Desktop, Tableau Public
Version(s): 8.1, 8.0, 7.0
Last Modified Date: 16 Aug 2016

Article Note: This article is no longer actively maintained by Tableau. We continue to make it available because the information is still valuable, but some steps may vary due to product changes.


You can use a reference line to mark a specific value or area on an axis in your view. However, when you enable a filter on the view—for example, a slider quick filter—the reference line remains in the view, whether or not its value falls within the current filter values. For example, suppose you have a reference line set to a specific date. If that date does not fall within the date range on the slider quick filter, the view may appear skewed, as shown in the following image.

To resolve this issue, you can create a dynamic reference line. This article shows how to create the following two types of reference line:

  • One that appears in the view only when the value it is set to is included within the date range specified by a date filter.

  • One that lets you hide the reference line when the view is skewed as in the image above.

Download and use the attached workbook, DynamicReferenceLine.twbx, and follow the steps below to create the objects described. The procedures assume you have Tableau Desktop 8.1 or later. However, it should be possible to follow the procedure in earlier versions of Tableau Desktop.

Create a calculated field

Create a calculated field that displays a reference line in the view on April 1, 2007. The reference line only appears if the date range set on the filter includes this date. Open the Calculation Method worksheet to begin.

Step 1 

Select Analysis > Create Calculated Field.

Step 2 

In the Calculated Field dialog box, complete the following steps.

  1. For Name, type Reference Line April.
  2. In the Formula box, build the following formula (or just cut and paste):

    IF WINDOW_MAX(MAX([Month])) > DATE("4/1/2007")

    AND WINDOW_MIN(MIN([Month])) < DATE("4/1/2007")

    THEN DATE("4/1/2007")

    ELSE NULL END

  3. Confirm that the status message indicates that the formula is valid, and then click OK.

The reference line does not appear, because the relevant date is not in the view.

Step 3 

From the Dimensions pane, drag the new field to Detail on the Marks card. Then right-click it and select Continuous.

Step 4 

Right-click the date axis in your view (along the bottom), and select Add Reference Line, Band, or Box.

In earlier versions of Tableau Desktop, this command is Add Reference Line.

Step 5 

In the Add Reference Line, Band, or Box dialog box, do the following:

  1. Verify that Line is selected at the top.
  2. In the Value drop-down list, select the calculated field you created earlier in this procedure.
  3. In the Label drop-down list, select Value.
  4. Click OK.

You can leave the other fields set to their default values.

Step 6 

Adjust the date range on the Month slider quick filter so that it includes the data 4/1/2007.

If the upper limit of the date range is later than 4/1/2007 and the lower limit of the date range is earlier than 4/1/2007 (a range that includes the reference line’s date), the reference line appears in the view. If the upper limit is earlier or lower limit later than 4/1/2007, the reference line is removed from the view.

Create a parameter control

Alternatively, you can create a parameter control that acts as an on-off switch for the reference line, and you can specify whether the switch is turned on or off. As in the earlier example, the reference line is set to 4/1/2007. Open the Parameter Control Method worksheet to begin.

Step 1 

Right-click the Data window, and select Create Parameter.

Step 2 

In the Create Parameter dialog box, do the following:

  1. For Name, type Reference Line Control.
  2. For Data type, select Boolean.
  3. For Current value, select True.
  4. Under Aliases, do the following:
    • In the True text box, type On.
    • In the False text box, type Off.
  5. Click OK.

Step 3 

Select Analysis > Create Calculated Field.

Step 4 

In the Calculated Field dialog box, complete the following steps.

  1. For Name, type Reference Line.

  2. In the Formula box, type or paste the following:

    IF [Reference Line Control] THEN DATE("4/1/2007")

    ELSE NULL END

  3. Confirm that the status message indicates that the formula is valid, and then click OK.

Step 5 

From the Dimensions pane, drag Reference Line to Detail, right-click the field, and then select Exact Date.

Step 6 

In the Parameters pane (at the bottom of the Data window), right-click Reference Line Control, and select Show Parameter Control.

Step 7 

Right-click the date axis in your view (along the bottom), and select Add Reference Line, Band, or Box.

Step 8 

In the Add Reference Line, Band, or Box dialog box, do the following:

  1. Verify that Line is selected at the top.
  2. In the Value drop-down list, select Reference Line.
  3. In the Label drop-down list, select Value.
  4. Click OK.

You can leave the other fields set to their default values.

Step 9 

Adjust the date range on the Month slider quick filter so that it includes the data 4/1/2007.

Now you can control whether the reference line appears in the view by selecting either On or Off in the Reference Line Control parameter control.

 

Alternate Search Terms:index, date, date filter, reference, calculation
Did this article resolve the issue?