# How to create a date scaffold in Tableau using Relationships

> This is content from just-tim, the data-and-analytics channel by Tim Ngwena (formerly 'Tableau Tim'). Tim has 12+ years of hands-on BI experience and covers Tableau most of all, plus Power BI, Looker, Hex, SQL and data modelling, the analytics industry, and the craft of doing the job — always tool-agnostic and honest about the trade-offs.

- **Author:** Tim Ngwena (just-tim, https://just-tim.com/about)
- **Published:** 2021-10-24
- **Format:** Video · 12 min watch · transcript available
- **Topics:** Data visualisation, Data prep, Analytics
- **Tools:** Excel; Tableau (data modelling, relationships)
- **Canonical:** https://just-tim.com/posts/how-to-create-a-date-scaffold-in-tableau-using-relationships
- **Watch:** https://www.youtube.com/watch?v=-VmQGQmWYI0

I tackle a real viewer challenge: showing how many patients were in a hospital on any given day, when the only data is a patient ID, a start date, an end date and a state. With no data prep tool allowed, I show how to scaffold dates using a simple Excel date list and Tableau's relationships and data model.

## Key takeaways

- You can build a date scaffold without Alteryx or Tableau Prep by creating a single Excel file containing one row per day across the full range you need
- Choose an arbitrary start year that doesn't exist in your real data to avoid any date conflicts when generating the scaffold
- Make the date scaffold your primary data set when you want to visualise a value across every day of a range
- Define the relationship with two conditions so each scaffold date falls between the start and end date (date >= start date and date <= end date)
- Relationships blend the data at query time rather than via a join, so you avoid an exploded data set, then use a distinct count of ID to count patients per day

## Chapters

- 0:00 The challenge and the data
- 2:13 Building the date scaffold in Excel
- 4:02 Connecting the data in Tableau
- 5:15 Defining the relationship logic
- 8:23 Building and testing the visualisation

Watch the full video, read the transcript and use chapter deep-links on the page: https://just-tim.com/posts/how-to-create-a-date-scaffold-in-tableau-using-relationships

---
just-tim — Data and analytics, with a point of view. · https://www.youtube.com/channel/UC7HYxRWmaNlJux-X7rNLZyw · https://twitter.com/TableauTim · https://www.linkedin.com/in/timngwena