Drilling down by category was a topic in my previous blog. And this time I would like to blog about how to do a drill down action by time.
I am using superstore this time as a sample data set too. Please feel free to follow along.
- Create the custom date from the date field (Order date in this case)
Creating the exact date field is for us to make sure the date field stays within the level we want. As usually when it comes to date type of data there are few level of hierarchy we can change into (year, quarter, month etc.).
Make sure you choose "Date Value" instead of "Date Part". You can imagine the difference between them is Date Part is "Blue Pill" in Tableau which is being treated as categorical value whereas Date Value is the "Green Pill", will be treated a continuous value.
2. After that create a set with the custom date we have just created
Randomly tick a few dates from the set, just need to bear in mind that the date we chose would be used as the date drill down later. After choosing a few dates. Time to build the master line chart. Put the custom date into columns and this time we are using Sales as the measure.
When you are dragging the custom date into column, I would drag with holding a right click and choose the continuous option.
Your master line chart should look like this now (if you are using Superstore as well).
3. Now we have to create some calculated fields to compute the start date and end date within the set we have just created
Create the calculated fields as below:
These calculated fields will return the maximum and minimum dates within the selected date range of yours (Remark: they are Fixed LOD calculation as you can see the curly bracket {, or mustachio according to Andy. I did not specify the FIXED syntax is because Tableau will default the LOD at fixed level if you do not specify anything).
After that right click both of them to drag them into the details shelf of the sheet, select the "continuous" option when the window pops up.
4. Time to add the reference band
After adding the max date and min date into the details we will be creating the reference band. Go to the analytic pane next to the data pane, drag the reference band option to the date option.
You should see a configuration window pops up with "Band From" and "Band To" options, this is where the min max date calculation come into usage.
Choose the min date cal in the "Band From" option, keep as minimum and max for "Band To" keep as maximum, and label and tooltip I would usually disable them to avoid any confusion.
After that the chart should look like something similar as above. Now it's a bit of formatting time to show the selected date range line.
5. Create another calculated fields for the selected date ranges in order to colour them
Create a calculated field as below and drag the field into the colour shelf:
This is telling Tableau to return a boolean value that whether a date is within the selected date range or not.
Your line chart might look bit different from this point, you need to change the colour pill into an attribute. Refer to below GIF:
6. Now onto the change set action
Now we have our master line chart and a selected date range, time to give user control to change the set dynamically. Go to "Worksheet" -> "Action" -> "Change Set Action", remember to change the action to dashboard action if you are building a dashboard.
The master line chart and the dynamic set action is done! Now we are doing a drill down for some other line charts.
7. Drill down (Try region this time maybe?)
Duplicate the master line chart, and then drag the date set into filter. After that put the dimension you want to break down on Rows (I am going to use Region this time).
Then they are ready to be put together as a dashboard. Start a new dashboard and put both sheets in. Few things need to be done: change the sheet action into dashboard action, remove the reference band for the region line chart.
After that you should have a backbone for the date drill down dashboard! I am not going to go over formatting in this long dull blog again. But if you read until this very last sentence I appreciate your time. And hope you find it useful!
