For a recent client project I created a KPI dashboard that was able to show varying date ranges such as the last month, the last 3 months, last 6 months as well as data for each year in the data set. I thought it would be a good use case for parameter actions to create some dynamic buttons for each year in the data set as well as buttons for a set number of months.
This is a step by step guide of how I created this, using superstore data as an example. The functionality of the dashboard is demonstrated below:
First to create buttons for the last month, 3 months and 6 months a small table with these values was added as another data source. The number of months to show was the value as an integer that was put on columns.
Then to create dynamic buttons for each year in the data set, the year of the Order Date was put on columns on another sheet. It is dynamic because if new data in another year is added a new column for that year will appear. I also added a calculation so the last year of the data set would end with YTD (year-to-date).
Next a parameter called ‘Chosen Time Period’ was created. This was set to an integer with all allowable values. This means a parameter action would allow either sheet to update the parameter. If the last X months was chosen this value would be 1, 3 or 6. Meanwhile if the years were chosen, in this data set it would be 2015, 2016, 2017 or 2018. If a new year such as 2019 was added this would also work as the parameter was set to ‘All’ allowable values.
A calculated field called ‘Is Chosen Time Period’ returns a true or false value for each row based on the value of the parameter previously created. If the value of the parameter is less than 12 (i.e. 1, 3 or 6) any orders for the 1, 3 or 6 months prior to the current month were marked as ‘True’. Otherwise if it is greater than 12 (i.e. 2015, 2016, 2017, 2018), the rows with an Order Date in the specified year became ‘True’.
Now to make the magic happen two parameter actions were created. Each updates the target parameter ‘Chosen Time Period’, one based on the Month sheet, and one based on the Year sheet.
Finally just as a formatting bonus a dynamic title was created to display which time period was currently being viewed. This was simple enough to create based on the value of the parameter as seen below.
The final dashboard can be seen below. Feel free to view it or download it from Tableau Public here.
