How To Tableau: Date Scaffolding

Find out how to create charts in Tableau to show the number, or value, of items that were open or in progress each day, week or month across a given time period.

Last Updated: 14th January 2021 | Tableau Version Referenced: 2020.4 | Contact: alan.m.murray@icloud.com

Introduction

In this “How To Tableau…” we show you how easy it is to make a chart similar to the one below which shows the number or value of open items across a given time period, typically days or weeks.

This is a pretty typical request and is usually along the lines of ‘show me the number of these items that were open each week’, examples of where you might use this include seeing:

• How many tickets or jobs were open, or at a given status, each day and how this changes over time.

• Seeing the value of potential sales opportunities that are open each week to see if enough new opportunities are being created to back fill won or lost opportunities.

• Seeing how many active customers you have each week to understand churn, or tracking progress through your customer onboarding process.

We’ve even see this used in airlines to see how many customers have booked flights but those flights haven’t yet departed to help with their risk metrics. Essentially this can be used with any data where you have a ‘start’ and ‘end’ date to see the change in number of in progress items across a time period.

In a spreadsheet program this is pretty easy to do, you simply create a list of dates then write a formula to work out the relevant value for each date and plot that on a chart:

We’re going to show you how to do this in Tableau (Spoiler alert: It’s not that much different from what you have to do in a spreadsheet!).

Where to start?

Looking at how this is done in Excel above we can see that what we need is a list of dates so that we can work out how many items had been opened but not closed between those dates.

In Excel you would just type the dates into a cell and write a formula to get the values you want, in Tableau you don’t really have anyway to create that list of dates you want to reference - luckily the solution is pretty simple as all we need to do is create a ‘date scaffold’ file which gives us the required dates.

Create a date scaffold

This part is really simple - all you have to do is create a list of the dates you’re interested in, we recommend creating it right in the spreadsheet (or database) that your data is in to make it easier but you can create it anywhere.

We’ve also created some date scaffold csv files that you can use here: Date Scaffold CSV File(s)

We’re using Excel in this example and we’re looking at each day in January, here’s the step by step instructions in case you need them:

1. Add a new sheet in your Excel file and name it ‘Dates’

2. In Cell A1 enter ‘Date’ as your heading.

3. In the cell below enter the first date you want to consider, in our example we’re looking at every day in January 2021 so we enter ‘01/02/2021’ (Note: We used the british date format of DD/MM/YYYY but yours will be whatever date format is used locally).

4. Now fill the cells below with rest of the dates you want to consider (Tip: In most spreadhseet programs if you enter the next date in the cell below then highlight the two cells and ‘drag’ the cursor down it will automatically populate for you).

5. Save and close your file (it’ll need to be closed for when we connect to it in Tableau)

Next we’re going to connect to that in Tableau and show you how to work out the required values for each day.

Connect the data in Tableau

First of all connect to your new data file as follows:

1. Open Tableau Desktop.

2. Click Connect - To a File > Microsoft Excel (or the program/source you created the date scaffold in).

3. Select the date scaffold file you created and click Open.

You should have a screen that looks like this:

We’re now going to join the date scaffold you created to your data as follows:

4. Drag the Dates sheet from the Sheets list to where it says ‘Drag tables here’.

5. Do the same with the sheet containing your data from the Sheets list, in our example it’s the Transactions sheet. When you do so the Dates sheet you already dragged should be outlined in orange and an orange ‘noodle’ should appear to connect the two sheets.

6. An ‘Edit Relationship’ box like the one below should appear:

   

7. Click Date under the fields for the Dates fields.

8. Click the little black arrow and select >= from the drop down box.

9. Select Start Date under the fields for the Transactions fields.

10. Click the ^ Close option.

11. Click + Add more fields.

12. Click Date under the fields for the Dates fields.

13. Click the little black arrow and select <= from the drop down box.

14. Select End Date under the fields for the Transactions fields.

15. Click the ^ Close option.

What you have should look like this:

Here’s all the steps together:

What we’ve done here is join our data to the date scaffold to create a row of data for each item that was in progress on each date in our scaffold file.

We can now use this to create our chart in Tableau.

Create the chart

1. Click Sheet 1 under the Go to worksheet pop up.

2. Under Tables on the left hand side, right click the Dates field under the Date table and drag it to the columns.

3. Select DAY(Date) from the green continuous dimensions in the list as below, or the interval you want such as MONTH, WEEK etc. (right clicking as you drag allows you to select this before you drop the dimension onto columns, you can drag it on normally and then click the pill to change the date type if you prefer)

   

4. Drag the measure you want to see in the chart to the rows. In our example this is ‘Values’, make sure it sets to Sum when you drag it.

That’s it, you should now have a chart that looks like this (we’ve formatted ours to look a bit better):

This now shows for each date the total value of any transactions that had started but not ended on the given date.

See it in action here:

---

Summary

Hopefully this shows you how easy it is to this in Tableau, here is a quick summary of each of the steps:

Step 1: Create a date scaffold

Step 2: Connect the data in Tableau

Step 3: create the chart

And that’s it done!

---

Notes

Note 1: When doing this Tableau will create a new row for each row in your data that matches the condition you’ve set for the date join and this can quite quickly result in a huge increase in the size of your data. Imagine you had just 1,500 rows of data, if you did this across a whole year and 1,000 rows matched each day you’d have 365,000 rows or a 200 fold increase in the size of your data.

Note 2: As your multiplying your data take not to also multiply your results on charts not concerned with the date scaffold. Date relationships in the latest versions of Tableau are pretty good at dealing with this but in older versions if you have a row of data that is duplicated across multiple dates it could also duplicate the result. If this happens either filter on the latest date or use a MIN or AVG calculation.

---

Resources

Below you can find links to the example files used as well as other useful resource files.

• Download Tableau Packaged Workbook

• Download Example Excel File

• Date Scaffold CSV File(s)

For the data scaffold file your company may have a date reference table in your database for you to use, this is usually a list of all possible dates which you can then group or filter to your requirements. If this exists you can connect to this in the same way as the Excel file above, if it doesn’t exists it’s worth seeing if you can create this in your database as it does come in handy.

---

Version Control

First Published: 14th January 2021

Latest Tableau Version Referenced: Tableau 2020.4

Contact: alan.m.murray@icloud.com

Updates:

No updates since first publishing