KNOWLEDGE BASE

Create a Relative Date Filter For An Arbitrary Range of Dates


Published: 12 Sep 2014
Last Modified Date: 14 Sep 2015

Question

How to create a relative date filter for a set of dates in which the MAX is not TODAY().

Environment

Tableau Desktop

Answer

The following instructions can be reviewed in the workbook attached below.

Step 1: Create the basic view 

  1. Drag Order Date to the Rows shelf.
  2. Right-click Order Date on the Rows shelf and select More > Custom > Month/Day/Year.
  3. Right-click Order Date on the Rows shelf and select Sort > Descending.
  4. Drag Order ID to the Rows shelf.

Step 2: Create a calculated field for the function INDEX

  1. Select Analysis > Create Calculated Field.
  2. In the dialog box:
    • Under Name, enter INDEX
    • Under Formula, enter INDEX()
  3. Click OK.
  4. In the Measures pane, right-click INDEX and select Convert to Discrete.
  5. Drag INDEX to the Rows shelf and place in between MDY(Order Date) and Order ID.
  6. INDEX can be hidden from the view by right-clicking INDEX on the Rows shelf and clearing the selection for Show Header. However, it is recommended to leave the field visible until the directions are complete so that the field's behavior may be observed throughout the remainder of the directions.

Step 3: Create a Table Calculation to adjust the manner in which INDEX is computed 

  1. Right-click INDEX on the Rows shelf and select Edit Table Calculation.
  2. For Compute Using, select Advanced....
  3. In the dialog box:
    • Move Month, Day, Year of Order Date from the Partitioning pane to the Addressing pane.
    • Move Order ID from the Partitioning pane to the Addressing pane.
  4. Click OK.
  5. From the At the level drop-down menu, select Month, Day, Year of Order Date.
  6. Click OK.

Step 4: Create a new Time Range Selection parameter

  1. Right-click in the Data pane and select Create Parameter.
  2. In the dialog box:
    • Name the parameter. In this example, the parameter is named "Show By Latest".
    • For Data Type, select Integer.
    • For Allowable Values, select List.
    • For the List of Values, enter 30, 90, and 365 under the Value column, and Last 30 Days, Last 90 Days, and Last 12 Months under the Display As column.
  3. Click OK.
  4. In the Data pane, right-click the Show By Latest parameter and select Show Parameter Control.

Step 5: Create a calculated field to act as a Boolean filter comparing INDEX and the parameter value.

  1. Go to Analysis > Create Calculated Field.
  2. In the dialog box:
    • Name the calculated field. In this example, the calculated field is named "Date Filter".
    • Under Formula, enter INDEX()<=[Show By Latest]
  3. Click OK.
  4. Drag Date Filter to the Filters shelf and select True from the dialog box.
  5. Right-click Date Filter on the Filters shelf and select Edit Table Calculation.
  6. For Compute Using, select Advanced....
  7. In the dialog box:
    • Move Month, Day, Year of Order Date from the Partitioning pane to the Addressing pane.
    • Move Order ID from the Partitioning pane to the Addressing pane.
  8. Click OK.
  9. From the At the level drop-down menu, select Month, Day, Year of Order Date.
  10. Click OK.
Did this article resolve the issue?