Parameters and Dynamic Date Level Calculations in Tableau
Ever wonder how to dynamically change date level and compare difference from previous date level depending on the user’s choice with just one click. This article provides a tutorial on how to achieve this by utilizing the parameters and calculations. After following through this article, you will be able to dynamically view the numbers and charts with just one click away in your dashboard.
Let’s dive in.
SITUATION — When working with operational centric data, often time your stakeholders want to see multiple time-series charts on different date level (day/week/month). And what if they ask to include more insights on the difference between current date level verse previous date level?
How to give user such flexibility with just one control over the entire dashboard as shown below?
TASK — For this tutorial, I will demonstrate total orders by day using superstore data from Tableau on the following areas:
- Adjust date level between day, week, month accordingly for the bar chart shown above.
- Calculate the difference between previous date level from current date level depending on the date level. (i.e., Compare total orders from previous month to current month)
- Both 1 and 2 are controlled by one parameter on dashboard.
ACTION — We will be using the superstore dataset from Tableau.
Step 1. Create a dynamic order date field with “Select Date Level” parameter.
You need a parameter to perform the dynamic date level aggregation. Make sure the values are lowercase.
Step 2. Create a grouped order date field which will replace the default [Order Date].
You need a calculation filed to bring the parameter to life, right? We want to group the order date by the day/week/month level that we just created in prior step.
With the parameter and the field above, you can create a dynamic view (New Quantity by Order Date) based on different date level shown as below.
- Drag [Order Date Grouped] to Columns and [Quantity] to Rows
- Right click on the [Select Date Level] parameter and select “Show Parameter”.
Step 4. You need two prerequisites fields before we can work on the difference calculations.
- A calculated field: TODAY()
- A Date Anchor Parameter using the Today calculated field.
Step 5: Then we can create three calculation fields to get the difference in number of orders between current date level and previous date level.
First let’s create separate field for total orders based on current date level and previous date level.
Finally, we need a field to show the difference from Current CNT on new orders to Previous CNT on new orders.
- Name a field Orders Diff, enter the following calculation, then click OK:[Current CNT on new orders]-[Previous CNT on new orders]
Step 6: Create the view.
- Drag [Current CNT on new orders] and [Previous CNT on new orders] to Columns and Select Dual Axis
- Right click on top axis and select Edit Axis. Make sure you check “Synchronize dual axes”
- Change mark type to Gantt Bar for [Previous CNT on new orders] and Bar for [Current CNT on new orders]
- Drag [Orders Diff] to Color. Click Edit Color and Select Custom Diverging Palette with stepped color in 2 steps, check Center and enter 0.
- Drag [Orders Diff] to Label, right-click and select Format, under Pane — Numbers — Custom and enter the following ▲ #,##0;▼#,##0
- (Optional) To make a self-serve dashboard, we also need couple calculations for tooltip, bar title, chart descriptions, etc. You can download my workbook in Tableau Public to get these out of your way.
Result —Congratulations on completing this tutorial! Now you can use just one parameter control the entire dashboard on grouping date level and calculations.
I found this dynamic grouping parameter extremely neat and powerful in some cases. Instead of having multiple tabs to show different calculations and date level, now you can aggregate them into one tab.
Here is my workbook if you want to download and play with it. Can’t wait to see what you come up with this!
