Dynamic Date Granularity for Analysis

We may need to create a trend chart according to variable date granularities, such as day/week/month/quarter etc via a parameter. This can be done fairly easily through 2 steps:

- Create a parameter Select Date Grain that has options as day/week/month/quarter

- Create Date Grain as a new date dimension

  •     DATETRUNC(Select Date Grain, Order Date)

That's it! We can use the above to create bar or line chart. Via the parameter, we will be able to analyze time series data at different granularity.

Show Missing Date Grains Dynamically

In Tableau, there is a native "Show missing dates" or "Show missing values" function, which allows us to add dates when data records are missing at those dates. This is also known as date/data densification. This works for any of static date grains, that is with dimensions like Month(Order Date), Quarter(Order Date) etc.

But this won't work when using dynamic date grains whose formula were described previously.

Here is a solution we found. The key is using date differences vs the max date as a dimension.

- Create a granular DateDiff field versus the Max Date Grain in the data set

  • Max Date Grain={MAX([Date Grain])}
  • DateDiff = DATEDIFF([Select Date Grain],[Date Grain],[Max Date Grain])

- Create bins based on DateDiff with step = 1. Place this bins field DateDiff (bin) on the Rows first. Right click on it and select "Show missing values". Then drag it to the Details card.

- Create a densified date field that computes along the above bins

Here we created a quarterly line chart showing missing quarters. Note that the date pill is discrete. The continuous green pill would show every missing dates instead of grains.

The workbook can be found here.

Solving The Problem of Calculating Granular Differences/Changes

We can use Tableau's native quick table calculations to calculate the change ratio between two adjacent date grains in usual cases where data is available at every grains.

It becomes tricky when there are missing dates or missing weeks/months/quarters. In this case, the granular differences or change percentages will be calculated between two successive non-null data points, instead of between two adjacent dates! This result may be OK for some, maybe not be good enough for others.

In case we want to always calculate granular changes between two adjacent dates with/without missing date grains, we can use the above technique to solve it.

4

View comments

  1. Nice Post! It's Really awesome please keep writing these typs of content

    ReplyDelete
  2. Is there a way to do this with a date range filter?

    ReplyDelete

(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. 

0

Add a comment

Blog Archive
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.