Consider this scenario – when your users open a dashboard, you want it to display a recent period, let’s say the latest 4 weeks. But at the same time, your users want to be able to manually select the from and to dates themselves. Simple right? Tableau should be able to do that out the box right? ….right?
This has bugged me previously, and there are certainly a number of ways around this. A blog on the Dataism wordpress site goes through one solution where a parameter is used to switch between latest N and custom date ranges, and of course there’s the ever reliable Flerlage twins who detail a number of Extensions that can do this, but I thought I’d try and give it my own flavour, to see if there was a way to do this without using Extensions, and without forcing a user to have to manually choose an option.
And so, I discovered a way involving 3 simple calculated fields and 2 parameters, and I thought if this can help someone else in the same situation, it would be worth sharing.
To start with, we need a dataset with a date field. In this case, we’re going basic and just going to use the ‘Sample – Superstore’ dataset
Let’s just create a very basic chart – a bar chart plotting the SUM of Sales against Region
Now we can start with the dates
We will need to create 2x calculated fields that will form the basis of our default ‘Date From’ and ‘Date To’ values
First off, create a field called ‘Date To’ set up as follows:
{FIXED:MAX([Order Date])}
Note that this is fixed to the last date in the dataset. If your data is dynamic and is updating every day, then the ‘{FIXED:MAX([Order Date])}’ can be replaced with the TODAY() function, or some variation thereof
Next, create a field called ‘Date From’ set up as follows:
{FIXED:MIN(DATEADD('week', -4, [Date To]))}
A few things to note here…this field is calculating a date 4 weeks prior to the last date in the data (created via our ‘Date To’ field). If you want to change the default period then you can do so by amending the date part (‘week’) and the interval. To calculate the previous 4 weeks, we must use a ‘–‘ in front of the interval value
Final note on this – you can see that we’ve wrapped this in a FIXED LOD. This might not always be necessary depending on your data, but this community forum article explains why this might be required
Basically, with DATEADD, Tableau cannot be sure that the date is one value, and as parameters need to be a single value, it won’t be selectable when we go on to create our parameter later. Wrapping it in a FIXED LOD will make sure of that
Next, we’ll create our parameters that our end users will use to interact with the views
Create a parameter called ‘Date From Parameter’ set up as follows:
Note that you will need to set this as ‘Date’ and choose the ‘Date From’ field in the ‘Value when workbook opens’ menu. If you cannot see your ‘Date From’ field, check that you have wrapped it in the FIXED LOD as described previously
And now our last parameter called ‘Date To Parameter’:
Again choose ‘Date’ as the type and choose our ‘Date To’ field in the ‘Value when workbook opens’ field
Nearly there, just one final calculated field to create called ‘Date Filter':
[Order Date] >= [Date From Parameter]
AND
[Order Date] <= [Date To Parameter]
This field will return a Boolean statemnt (TRUE or FALSE)
Drag this field to the ‘Filters’ card and select ‘True’ (i.e. we want the date to fall between the ‘Date From’ and ‘Date To’ values)
And that’s all there is to it!
When your workbook opens it will filter the data to only show the last 4 weeks of data (or whatever period you have chosen), and then, by including the ‘Date From Parameter’ and ‘Date To Parameter’ on your dashboard for your users to interact with, they can manually override these values to choose a custom date range if they wish, without having to click a button first
I hope this has been useful - let me know in the comments!
コメント