KNOWLEDGE BASE

Controlling the Level of Drill Down for Dates


Published: 27 Feb 2013
Last Modified Date: 13 Nov 2017

Issue

How to limit the levels of granularity available when drilling down on a date dimension.

Environment

Tableau Desktop

Resolution

Use the following procedure to limit the level of drill down. See attached workbook for details.
 

Step 1: Create the Year Level of the Hierarchy

  1. Right-click Order Date in Dimensions and then select Create > Create Calculated Field.
  2. Name the field; in this example: Date Year.
  3. Type the following formula: DATEPART('year',[Order Date])and then click OK.
  4. Right-click Date Year under Measures and then select Convert to Dimension.
Step 2: Create the Quarter Level of the Hierarchy
  1. Right-click Order Date in Dimensions and then select Create > Create Calculated Field.
  2. Name the field; in this example: Date Quarter.
  3. Type the following formula: DATEPART('quarter',[Order Date]) and then click OK.
  4. Right-click Date Quarter in Measures and then select Convert to Dimension.

Step 3: Create the Hierarchy

  1. Right-click Date Year in Dimensions and then select Hierarchy > Create Hierarchy...
  2. Name the Hierarchy; in this example: Manual Date Hierarchy and then click OK.
  3. Right-click Date Quarter in Dimensions and then select Hierarchy > Add to Hierarchy > Manual Date Hierarchy.

Step 4: Create the View

  1. Drag Sales to Rows.
  2. Drag Date Year to Columns.
  3. Use the plus (+) sign to drill down the manual hierarchy.

Cause

Ability to control the level of granularity when drilling down a date dimension is functionality not built into the product.
Did this article resolve the issue?