Blending Dates via Scaffolding

This is a sequel to Lossless Data Blending via Scaffolding. The blending here is also lossless.

Date dimension is one of the most important dimensions in data structure. Most data are transactional ones with a timestamp attached to each transaction record. Needless to say, time series analysis is one of the most important analysis methods.

Let's get into the problem statement based on a simple example: two stores have their sales tallied in two tables (two data sources). The headquarter wants to view the overall sales along time.

Here are the very simplistic reports from the two stores:

The dates in the two stores are non-inclusive sets. Let's see how to use scaffold to blend the two tables together and create a single chart to view the overall sales.

Scaffolding
As we mentioned in Lossless Data Blending via Scaffolding, we need to build a superset of the dates in both tables. For date dimension it is much easier. It is a two-row table with the min date and the max date of combined set of dates in the two tables:

Note that the Scaffold Primary vs Secondary

1.Show Missing Values
Drag the Date to Columns shelf and turn on "Show Missing Values" in Scaffold's date pill, we will get all the dates in between. This is the KEY in blending date dimension!!

2.Blend Dates
Make sure all date dimensions from the secondary sources are linked with the primary.

3.Use ZN() to aggregate
We then create a measure Total Sales as

ZN(SUM([Store A].[Sales]))+ZN( SUM([Store B].[Sales]))

The ZN() function will fill zero at data marks where it is null. And this allows the two sales values to add. Otherwise, Null + values = Null. Without ZN(), the Total Sales would have been Null as well. So it is a must to use ZN().

Scaffolding with Parametric Dates

Data is dynamic. Especially the end date may not be fixed. We need to change the end date from time to time. It would be hard to change the static scaffold above.

We can use parameters for dates. Thus a parameter's default value and maximum value can be modified easily.

Here is the scaffold:

The we create a calculated field to set up Date dimension:

Case [Record]
WHEN 1 then [Start Date]
WHEN 2 then [End Date]
END

Here is the view of the resulting dashboard. Click it to go the interactive version.

This technique has been applied in this calculation:
Taking Stock with Start and End Dates

(Refresh the page if you want to view the gif image multiple times. Or go to Tableau Public and click the button at the top-right corner.)

Jake and I collaborated on a dashboard. He told me that he learnt a way to create an in-place help page in Tableau. He first saw it at a conference somewhere and couldn't recall who the speaker was. So I am blogging here about it but the credit goes to somebody else. If anyone knows who the original creator is, leave a comment below.

The key idea is to float a semi transparent worksheet on top of the dashboard, where a help text box is strategically placed on top of each chart. This way, we can explain how to view each chart and what data points are important, etc. This worksheet is collapsible by a show/hide button.

Below I would like to show how this worksheet can be constructed.

1. Sheet with a single data mark.

Double click the empty space in Marks panel and add two single quotes. Make the null pill a text label. This creates a single null mark.
Set the view as "Entire View"

2. Create an show/hide button

Go to the target dashboard
Drag a floating vertical container to the dashboard, making it cover all the area of interest.
Drag the Single Null Mark sheet and drop it into the above container. Hide the sheet title.
Create an open/close button for the container and place the button at the top-right corner.

3. Add annotations

Format the sheet background opacity as 70% in the layout manager
Select area annotations and place them anywhere of interest.
Write help text and format it to highlight important messages.
The text can serve as functional guide and/or insight guide.

Here is an example. Feel free to download the workbook and explore. Click the "i" button at the top-right corner to view the in-place help.

Add a comment

Vizible Difference

In search of data visualization excellence

1. Sheet with a single data mark.

2. Create an show/hide button

3. Add annotations

Add a comment