What do you do if you always have to show the same number of date records, but I need to be able to change the date level: day, week, month, quarter, and year? How do I accomplish this in Tableau? For example: I want to be able to dynamically change between 24 months and 24 years in my visualization.
This can be fairly easily accomplished with a mixture of calculated fields, parameters, and filters.
First, we need to confirm that we have a data set with a date and at least one measure:
The data set I am using for this example illustrates the change over time price for bananas. Super exciting, right!
Next, we need to create our parameter that allows us to select the date level we are interested in:
I will be calling this parameter "Select Date View". We should use String as the data type and List for Allowable Values. Type in the values you want to summarize your data at within the list of values. For simplicity, I will only be using Monthly and Yearly. However, you can go to any granularity you would like (such as down to day).
Be sure to show your parameter in your visualization.
No, we will make our first calculated field:
This calculated field will filter out the the dates that we do not want to see. Specif to this example, we will only return the last 24 months or 24 years.
Overall, the calculated field returns 'Show' if all of the criteria is met, and 'Hide' if the criteria is not met. The statement first checks to see if 'Monthly' or 'Yearly' was selected in the parameters. Based off of that selection, we use the DATEDIFF() function to determine if our [Date] was within the last 24 months or years.
Next, we add this calculated field to the filters shelf and we filter to only show the 'Show' results:
Now we are ready for our second calculated field. This will be called "Dynamic Date View" This one will truncate the date at the correct level, which in this example is month and year:
We use another IF statement. However, in this example, if our criteria is not met, we return nothing.
Again, we double check which parameter string was selected, Monthly or Yearly. Based on this selection, we use the DATETRUNC() function to truncate to the month or year, based on the parameter.
Now, we add our Dynamic Date View to columns and Sum(Price) to rows:
Also, I think it is easiest to view if you change the Dynamic Date View pill to show continuous days. This way, it displays the first day of the month or year.
We are now complete! We can dynamically change our parameter to show the last 24 years or months.
Next Level Bonus
We can add even more parameters to further customize our view. Let's create a parameter where we can dynamically change the number of units in the range. For the example, we will change from just 24, to any value between 10 and 30.
First, we create another parameter:
I have named the parameter "Range". It will always need to be an integer, so I have selected that is the Data Type. I am also going to specify a specific range of 10 - 30. Go with whatever range answers your questions and is compatible with your data set.
Next, we need to edit our original calculated field, the Time Filter, to account for this new parameter:
The calculation is exactly the same, except for one thing. The static 24 has been replaced with our Range parameter. Now, we can select exactly what size range we want to use.
With the combination of filters, parameters, and calculated fields, we can create a view that adds even more customization for our end users. We can take this idea even further! Be able to select days, weeks, months, quarters, and years! Or, have another parameter that we can specify the date we start calculating back from.