Loading

Creating Relative Date Filters with Adjustable Anchor Dates

Publiseringsdato: Apr 30, 2025
Oppgave
How to create a relative date filter with an adjustable anchor date.
Trinn

Option 1 

Create a parameter that allows the user to select an anchor date for a fixed relative time range, for example the last 3 months from the 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 Choose Anchor Date.
    • For Data type, select Date.
    • For Allowable values, select All.
  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 Filter with Anchor.
    • In the formula field, create a calculated field similar to the following:
      DATEDIFF('month',[Order Date],[Choose Anchor Date]) <= 2
      AND DATEDIFF('month',[Order Date],[Choose Anchor Date]) >= 0
  3. Drag the newly created calculated field from the Dimensions pane to the Filters shelf.
  4. In the Filter dialog box that opens, select True, 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.
  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.
 


Option 2 

Create a parameter that allows the user to select an anchor date for a year to anchor date time range, for example the start of the year to the chosen 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 Choose Anchor Date.
    • For Data type, select Date.
    • For Allowable values, select All.
  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 Year to Anchor Date Filter.
    • In the formula field, create a calculated field similar to the following:
      DATEDIFF('year',[Order Date],[Choose Anchor Date]) = 0
      AND [Order Date] <= [Choose Anchor Date]
  3. Drag the newly created calculated field from the Dimensions pane to the Filters shelf.
  4. In the Filter dialog box that opens, select True, 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.
  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.
Flere ressurser

Option 1 will return the exact same results as setting a static anchor date in the Filter dialog. If there is data for dates after the chosen anchor date, this option will includes dates for the entire month of the anchor date. For example, if March 15 is chosen, the view will show dates between January 1 and March 31.

Option 2 will end the date range at the chosen anchor date. For eample, if March 15 is chosen, the view will show dates between January 1 and March 15.

For added flexibility, additional parameters can be created to define the date part, such as 'month' or 'year', and the number of date parts back the filter goes. For example, the calculation in option 1 might become:

DATEDIFF([Date Part Parameter],[Order Date],[Choose Anchor Date]) <= [# of Date Parts Parameter] - 1
AND DATEDIFF([Date Part Parameter],[Order Date],[Choose Anchor Date]) >= 0

For a dynamic anchor date, such as last month, the [Choose Anchor Date] parameter can be replaced with a calculated field that defines a dynamic date. For example, a calculation that always returns the first day of last month may look like:

DATETRUNC('month', DATEADD('month', -1, TODAY() ))

 

Knowledge-artikkelnummer

001458277

Vedlegg

Adjustable Anchor Date.twbx

1207 KB

 
Laster
Salesforce Help | Article