KNOWLEDGE BASE

## Controlling the Level of Drill Down for Dates

Published: 27 Feb 2013

### Issue

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

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.