9 December, 2021
min reading time
I help you get insights from your data. Easier. Faster.
A common question I receive from Tableau clients is the following: “I’d like the date range filter (start & end date) to automatically adjust, so the dashboard shows the most recent data upon opening it.”
Example of a date range filter:
If you are familiar with the filter's behaviour, this is certainly a valid question. When you open a dashboard with this type of filter, it will always revert to the settings when you published the dashboard. In this case: 01/11/2021 till 30/11/2021.
I'll explain how you can solve such a client's request.
Then my thought process was like this:
"I want the most recent date in my dataset to be in that parameter when the workbook opens, so if I take MAX([datefield]), that will work!"
Therefore I created a calculated field to do so. But still, I wasn't able to select the "Max Order Date" calculated field to be the current value of the parameter when the workbook opens.
The reason for this is that MAX(datefield) is an aggregation dependent on the filters used in the dashboard or sheet.
The value of it will change if other filters and parameters get changed. This leads to an unpredictable value, and thus we need to fix it so that it's independent of all the other filters.
To overcome this, the FIXED LOD expressions are a perfect fit! Let's change the Max Order Date calculation to be independent of other filters*.
*You might think: "Won't context filters mess up the LOD calculations?". In short: no they won't. The LOD calculations used for filling the parameter's current value are calculated before the worksheets are loaded. So even before context filters are applied. Thus it's safe to use context filters without worrying about impacting the parameters.
{ FIXED : MAX([datefield])}
or even shorter:
{ MAX([datefield]) }
Now, I can select the Max Order Date calculated field as input for the parameter's current value!
So, now every time someone opens this workbook or dashboard, this parameter fills with the most recent date available in the data source.
The next step to replicate the regular date range filter would be to create another parameter which we also prefill with a date upon opening the workbook or dashboard using the same method. This time, we need to create a negative offset from the previously created Max Order Date.
{ FIXED : MAX(DATEADD('month',-3,[Order Date])) }
[Order Date] >= [Order Date Start]
AND [Order Date] <= [Order Date End]
Tableau is designed to put the user first because data analysis should be about asking questions and not about learning software. With built-in visual best practices, Tableau enables limitless visual data exploration without interrupting the flow of analysis.
As the market-leading choice for modern business intelligence, the Tableau platform is known for taking any kind of data from almost any system and turning it into actionable insights with speed and ease. It’s as simple as dragging and dropping.
We are a full-stack provider and integrator, relying on extensive experience and best practices to find your unique optimal set-up allowing you to tell the data stories you are eager to tell.
I help you get insights from your data. Easier. Faster.
Read more articles of this author