In this first edition of Tableau Bites (a series of short and easy-to-read posts about Tableau useful tips) we will cover how to dynamically change the date level of a visualization depending on the user’s choice (year, quarter, month).
In our desired Tableau project (in my case i will use this dataset that contains information about bike sales in Europe as an example) let’s create a new parameter clicking in the dropdown arrow that is located in the top-left section of the interface, next to the search bar (Img. 1).
Next step is to set up the parameter. First, name it as ‘Date Level’ and fill the properties with the following settings (Img. 2).
Basically the parameter will have three string options: Month, Quarter and Year. These are the values that the user will be able to toggle to change the views of the data.
Now it is time to create the Calculated Field that will contain the logic to generate the desired result.
Again, click on the dropdown arrow located in the top-left section of the interface and click ‘Create Calculated Field’.
Name the calculated field as ‘Date Level CF’ and paste the following code snippet:
CASE [Date Level] // Year format: 2022 WHEN "Year" THEN STR(YEAR([Date])) // Quarter format: Q2-2022 WHEN "Quarter" THEN 'Q'+STR(DATEPART('quarter',[Date])) + '-' + STR(YEAR([Date])) // Month format: August-2022 WHEN "Month" THEN STR(DATENAME('month', [Date])) + '-' + STR(YEAR([Date])) END
This snippet is pretty much self-explanatory, but just to make it clearer it is a CASE statement that selects a specific output taking the parameter current selection as input. As an example, if the user selects the Quarter view, the data level output will be in the ‘quarter-year’ format like it is shown in the code.
Now that all the logic is ready, let’s put everything into the view.
Drag the Date Level CF pill into the columns container and your desired measure into the rows container, in my case i will use the Profit (Img. 3). Also, do not forget to right-click on the Date Level parameter and click ‘Show parameter’ to be able to toggle between the created date level views.
Now, if you dynamically change the parameter value, you will see how the date level in the X-axis changes.
Note that the dates displayed in the gif above are not ordered correctly. To solve that you can right-click on the Date Level CF pill on the columns container and click Sort to order the values by the Date field and show them correctly (Img. 4).
If you want to see a live demo of the final result, you can play around with it in the project’s Tableau Public page.
If you have specific doubts do not hesitate to reach me out via Twitter.
This project was featured on The weekly DataFam Roundup by Tableau.