KNOWLEDGE BASE

Creating Relative Date Filters with Adjustable Anchor Dates


Published: 11 Jun 2014
Last Modified Date: 24 Nov 2016

Question

How to create a relative date filter with an adjustable anchor date.

Environment

Tableau Desktop

Answer

The below steps create a calculation to show the three months prior to an adjustable anchor date.

Step 1: Create a Parameter

  1. In Tableau Desktop, connect to Superstore sample data.
  2. Right-click in the Data window and then select Create Parameter.
  3. In the Create Parameter dialog box, do the following, and then click OK:
    • Name the parameter. In the example workbook, the parameter is named Date Control.
    • For Data type, select Date & time.
    • For Allowable values, select List.
    • Click Add from Field, and then select Order Date.
    • For Display format, select March, 2011.
  4. Right-click the parameter and then select Show Parameter Control.

Step 2: Create a Calculated Field

  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    • Name the calculated field. In the example workbook, the calculated field is named Relative Date.
    • In the formula field, create a calculated field similar to the following:
      IF DATEDIFF('month',[Order Date],[Date Control])<=3 THEN "Show" ELSE "Hide" END
  3. Drag the newly created calculated field from the Dimensions pane to the Filters shelf.
  4. In the Filter dialog box that opens, select Show, and then click OK.

Step 3: Create the View

  1. Drag Order Date to Rows.
  2. Right-click Order Date on Rows, and then select Month May (2011).
  3. Right-click Order Date on Rows, and then select Discrete.
  4. Double-click Sales.
  5. Use the Date Control parameter control to select an anchor date.

Additional Information

For a more flexible calculation, in Step 2 above, the 3 can be replaced with a second parameter, such as [Parameter 2], which contains a range of integers such as 0-12. You can then adjust [Parameter 2] to control the range of dates displayed.

Did this article resolve the issue?