Missing dates in your data? Don’t panic, date scaffolding is our answer!!

Have you ever worked with time-based data in Tableau? you know how frustrating it can be when your dataset has missing dates, right? 

Whether it’s because of inactivity, gaps in data collection, or basically a lack of records on certain days. Missing dates can throw off your analysis and create odd looking and misleading visualisations. But there’s a solution:  We call this, ‘date scaffolding’.

Date scaffolding helps fill in those gaps by creating a complete date range that ensures every date, even ones with no data, and is then represented in your visualisations. This is especially useful for creating accurate and comprehensive time-based analyses, such as a line-chart. You may not want to see gaps in your dates. 

Let’s take a look at what date scaffolding is, why it’s important, and how you can easily implement it in Tableau.

So, what Is Date Scaffolding?

In simple terms, date scaffolding is the process of adding a complete date range to your dataset. Think of it as creating a framework or “scaffolding” for your data—no matter what the data actually contains, you still have a complete set of dates to work with. By doing this, you ensure that missing dates won’t look odd or have any negative impact on your calculations.

When to Use Date Scaffolding

Here are a few common use-cases where date scaffolding can make a big difference.

Sales Analysis – Ensure that your sales trends account for days with zero sales, even when there is no record in the data for that date.

Website Analytics – Track daily visitors, even on days with no traffic.

Inventory Management – Monitor stock levels, including days without updates or activity.

With using date scaffolding, you can:

Ensure every date in your analysis is covered.

Get accurate calculations, aggregations, like running totals, stay smooth and accurate.

Allow you to identify patterns in periods of inactivity or missing data.

How do I create Date Scaffolding in Tableau

Now that you understand the benefits, let’s talk about 2 ways in which to create a date scaffold dataset for using in Tableau.

Step 1:

Firstly, you need to build your date scaffold. To begin with, you need a list of every date in your range. I tend to start at the earliest date possible in your data, and go out to something like 2045-12-31. We can do this a couple of ways…

1. Create a Date Dimension (DateDim) Table in Your Database

If you have access to a database, you can write an SQL query to generate a sequence of dates. Here’s an example for SQL Server:

CREATE TABLE dbo.DimDate (
 DateID int NOT NULL IDENTITY(1, 1),
 [Date] datetime NOT NULL,
 [Year] int NOT NULL, 
 [Month] int NOT NULL,
 [Day] int NOT NULL,
 [QuarterNumber] int NOT NULL,
 CONSTRAINT PK_Dates PRIMARY KEY CLUSTERED (DateID)
)

— declare variables to hold the start and end date
DECLARE @StartDate datetime
DECLARE @EndDate datetime

— assign values to the start date and end date we 
— want our reports to cover (this should also take
— into account any future reporting needs)
SET @StartDate = ’01/01/2010′
SET @EndDate = ’12/31/2045′ 

— using a while loop increment from the start date 
— to the end date
DECLARE @LoopDate datetime
SET @LoopDate = @StartDate

WHILE @LoopDate <= @EndDate
BEGIN
 — add a record into the date dimension table for this date
 INSERT INTO dbo.DimDate VALUES (
  @LoopDate,
  Year(@LoopDate),
  Month(@LoopDate), 
  Day(@LoopDate), 
  CASE WHEN Month(@LoopDate) IN (1, 2, 3) THEN 1
   WHEN Month(@LoopDate) IN (4, 5, 6) THEN 2
   WHEN Month(@LoopDate) IN (7, 8, 9) THEN 3
   WHEN Month(@LoopDate) IN (10, 11, 12) THEN 4
  END 
   
 )  
 — increment the LoopDate by 1 day before
 — we start the loop again
 SET @LoopDate = DateAdd(d, 1, @LoopDate)
END

This query generates every day from 2010-01-01 to 2045-12-31.

2. Use a spreadsheet

You can also create a list of dates in Excel (or another tool) and import that into Tableau. Simply ensure you have one row for each date in the desired range.

Step 2: Connect Your Date Scaffold to Tableau

Once you have your date scaffold, you need to bring it into Tableau. You can either:

**Join**: If your date scaffold is in a separate table in a datanase or an external file such as Excel, join it with your main data source using the date field.

**Blend**: If you have your data in two separate sources, use data blending to link them via the date field.

**** Be sure to use a **left join**, meaning the date scaffold is on the left, ensuring that every date is kept, even if there’s no data for it.  ****

Step 3: Handling Missing Data

After joining your data and scaffold, you will probably see some dates with missing values. You can handle this by, replacing nulls with zeros. Use Tableau’s IFNULL() function to replace null values with 0, or another placeholder like “No Data.” You can also use ZN(your_field).

Step 4: Visualise Your Data

With your date scaffold in place, your visualisations should now display the complete time series, even during gaps in data. For example:

Line charts will show continuous trends without broken lines.

Bar charts will clearly indicate days or periods with no activity.

In Summary:

The next time you encounter missing dates, don’t panic. With a little scaffolding, you can bridge those gaps and unlock more powerful, accurate visualisations. 

Feel free to contact me and I can help you.