Date hierarchy (year, quarter, month, day) in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see sales trends broken down by year, quarter, month, and day to understand seasonal patterns and daily sales fluctuations.

📊 Data: You have sales transaction data including the date of sale and the sales amount for each transaction.

🎯 Deliverable: Create a Tableau dashboard with a date hierarchy filter that allows drilling down from year to quarter, month, and day, showing total sales at each level.

Sample Data

Date	Sales Amount
2023-01-05	150
2023-01-15	200
2023-02-10	180
2023-03-20	220
2023-04-05	300
2023-04-25	250
2023-05-15	270
2023-06-10	320
2023-07-05	400
2023-08-15	350
2023-09-10	380
2023-10-20	420

1

Step 1: Connect Tableau to the sales data source containing Date and Sales Amount columns.

Use 'Connect to Data' and select the data file or database with the sales data.

Expected Result

Tableau shows the data fields Date and Sales Amount in the Data pane.

2

Step 2: Create a date hierarchy in Tableau using the Date field.

Right-click the Date field in the Data pane, select 'Create' > 'Hierarchy', name it 'Date Hierarchy'. Drag Date into this hierarchy. Tableau automatically creates Year, Quarter, Month, and Day levels.

Expected Result

A 'Date Hierarchy' appears in the Data pane with Year, Quarter, Month, and Day levels.

3

Step 3: Create a new worksheet to visualize total sales by date hierarchy.

Drag 'Date Hierarchy' to Rows shelf. Drag 'Sales Amount' to Columns shelf and set aggregation to SUM.

Expected Result

A bar chart appears showing total sales aggregated by Year initially.

4

Step 4: Enable drill down on the date hierarchy to explore sales by quarter, month, and day.

Click the plus (+) icon next to Year in the view to drill down to Quarter, then Month, then Day.

Expected Result

The chart updates to show sales by Quarter, then Month, then Day as you drill down.

5

Step 5: Create a dashboard and add the worksheet with the date hierarchy visualization.

In Tableau, create a new dashboard and drag the worksheet onto it. Add a filter for the Date Hierarchy to allow interactive drill down.

Expected Result

Dashboard shows the sales chart with interactive date hierarchy filter allowing users to select year, quarter, month, or day.

Final Result

Date Hierarchy Sales Dashboard

+-----------------------------+
| Year  | Total Sales          |
|-------|----------------------|
| 2023  | ████████████████████ |
+-----------------------------+

Drill down:
+-----------------------------+
| Quarter | Total Sales         |
|---------|---------------------|
| Q1      | ███████             |
| Q2      | ████████            |
| Q3      | ███████             |
| Q4      | ████                |
+-----------------------------+

Further drill down to Month and Day shows detailed sales bars.

✓Sales are highest in Q1 and Q2 of 2023.

✓Monthly sales show peaks in April and July.

✓Daily sales fluctuate, with some days showing significantly higher sales.

Bonus Challenge

Add a filter to the dashboard that allows users to select a specific year and see the sales breakdown by quarter, month, and day for that year only.

Show Hint

Use a 'Year' filter on the Date Hierarchy field and set it to show as a dropdown filter on the dashboard.

Practice

1. What is the main purpose of a Date hierarchy in Tableau?

easy

A. To explore data by Year, Quarter, Month, and Day levels easily

B. To create new calculated fields based on dates

C. To filter data only by year

D. To sort data alphabetically

Solution

1. Step 1: Understand what a date hierarchy does

   A date hierarchy organizes dates into levels like Year, Quarter, Month, and Day for easy exploration.

2. Step 2: Compare options to this purpose

   Only To explore data by Year, Quarter, Month, and Day levels easily describes exploring data by these date levels. Others describe unrelated tasks.

3. Final Answer:

   To explore data by Year, Quarter, Month, and Day levels easily -> Option A

4. Quick Check:

   Date hierarchy = Explore by date levels [OK]

Hint: Date hierarchy breaks dates into levels for easy drill-down [OK]

Common Mistakes:

• Thinking it only filters by year
• Confusing with calculated fields
• Assuming it sorts alphabetically

2. Which of the following is the correct way to create a date hierarchy in Tableau?

easy

A. Write a SQL query to group dates by year

B. Drag a date field to Rows, then right-click and select 'Create Hierarchy', adding Year, Quarter, Month, Day

C. Use the filter pane to select Year, Quarter, Month, and Day separately

D. Manually type dates in a new column

Solution

1. Step 1: Recall Tableau's method to create hierarchies

   In Tableau, you create hierarchies by dragging fields and grouping them, not by SQL or typing dates manually.

2. Step 2: Identify the correct process

   Drag a date field to Rows, then right-click and select 'Create Hierarchy', adding Year, Quarter, Month, Day correctly describes dragging a date field and creating a hierarchy with Year, Quarter, Month, and Day.

3. Final Answer:

   Drag a date field to Rows, then right-click and select 'Create Hierarchy', adding Year, Quarter, Month, Day -> Option B

4. Quick Check:

   Create hierarchy by drag and right-click [OK]

Hint: Right-click date field to create hierarchy in Tableau [OK]

Common Mistakes:

• Trying to create hierarchy via SQL
• Using filters instead of hierarchy
• Typing dates manually instead of using fields

3. Given a date hierarchy with Year > Quarter > Month > Day, what happens when you double-click the Year field in Tableau's view?

medium

A. Tableau filters data to the current Year only

B. Tableau drills down to show data by Day directly

C. Tableau shows data aggregated by Year only

D. Tableau shows data aggregated by Month only

Solution

1. Step 1: Understand double-click on a hierarchy level

   Double-clicking a level like Year shows data aggregated at that level, not deeper levels.

2. Step 2: Match behavior to options

   Tableau shows data aggregated by Year only correctly states data is aggregated by Year only. Other options describe filtering or drilling down incorrectly.

3. Final Answer:

   Tableau shows data aggregated by Year only -> Option C

4. Quick Check:

   Double-click Year = aggregate by Year [OK]

Hint: Double-click a level to see data summarized at that level [OK]

Common Mistakes:

• Thinking double-click drills down multiple levels
• Confusing aggregation with filtering
• Assuming it shows data by Month or Day immediately

4. You created a date hierarchy but when you try to drill down from Year to Quarter, Tableau shows no data. What is the most likely cause?

medium

A. The Year field is not a date type

B. The data source has no date values

C. You forgot to refresh the data source

D. The Quarter field is not included in the hierarchy

Solution

1. Step 1: Analyze why drill down shows no data

   If drilling from Year to Quarter shows no data, likely the Quarter level is missing in the hierarchy.

2. Step 2: Evaluate other options

   Data source missing dates or Year not date type would cause bigger issues; refreshing data is less likely the cause.

3. Final Answer:

   The Quarter field is not included in the hierarchy -> Option D

4. Quick Check:

   Missing Quarter in hierarchy blocks drill down [OK]

Hint: Check all date levels are in the hierarchy to drill down [OK]

Common Mistakes:

• Assuming data source is empty without checking
• Forgetting to include all date parts in hierarchy
• Thinking refresh fixes hierarchy structure

5. You want to create a dashboard showing sales trends by Year, then allow users to drill down to Quarter, Month, and Day. Which approach best uses Tableau's date hierarchy feature?

hard

A. Create a date hierarchy with Year > Quarter > Month > Day and use it in the view with drill-down enabled

B. Create separate sheets for Year, Quarter, Month, and Day and link them with filters

C. Use only the Month field and filter manually for Year and Quarter

D. Create calculated fields for each date part and display them side by side

Solution

1. Step 1: Identify best practice for drill-down in Tableau

   Using a date hierarchy with levels Year > Quarter > Month > Day allows smooth drill-down in one view.

2. Step 2: Compare other options

   Separate sheets or manual filters are more complex and less user-friendly; calculated fields side by side don't enable drill-down.

3. Final Answer:

   Create a date hierarchy with Year > Quarter > Month > Day and use it in the view with drill-down enabled -> Option A

4. Quick Check:

   Date hierarchy enables smooth drill-down [OK]

Hint: Use one hierarchy for drill-down, not separate sheets [OK]

Common Mistakes:

• Creating multiple sheets instead of one hierarchy
• Using only one date part without hierarchy
• Relying on calculated fields instead of hierarchy