is your data source an excel file?
with custom sql you can try filtering with a date parameter where its between the 1st of the start date month and the last of the end date month.
In my actual project, I'm using custom SQL. However, in this example I'm using an Excel file as my data source.
Would you be willing to show how to implement that type of parameter?
Here's is what I did to implement a solution to return the results you are looking for.
1. Create your date parameter. You may need to update this from time to time.
1. Create two new fields for start and end date to be used in the filter
a. Start Date for Filter: DATETRUNC('month',[Start Date])
b. End Date for Filter: DATEADD('month',1,DATETRUNC('month',[End Date]))
3. Create your filter field. Date Filter: IF [Date Param] >= [Start Date for Filter] AND [Date Param] < [End Date] THEN 'Show' ELSE 'Hide' END
4. Drag Date Filter to filters and set to 'Show'
That should do the trick. Let me know if you have any questions or would like me to upload a 9.2 workbook.
This solution looks magical (like the vast majority of your work that I've seen) ! .. And yet, I'm struggling to implement it fully. If you don't mind, I think posting the workbook could be super helpful for my noob brain!
Also, can this type of solution account for events that span beyond 2 months? EG Janice decides to extend her workshop from October to next January?
Thank you so-so much again!
I've attached a copy of the workbook and this solution will work for date ranges spanning several months or even years. Basically I am returning all the records where the parameter falls between the start and end dates.
The challenge with your data is you want the parameter at the month/year level and your dates are at the day/month/level. So what I did with the calculated fields was to make the start date fall on the first of the month it is in using DATETRUNC, so 5/3/2016 would be converted to 5/1/2016. For the end date I set it to the first of the month then as well but then added one month so 6/4/2016 would be converted to 7/1/2016. We now have fields that will work well with a parameter set to the 1st of each month.
For example if I have a record with Start Date of 5/1/2016 and an End Date of 7/1/2016 parameter values of 5/1/2016 or 6/1/2016 will return 'Show' using the Calculated date filter
The formula for the filter is: IF [Date Param] >= [Start Date for Filter] AND [Date Param] < [End Date for Filter] THEN 'Show' ELSE 'Hide' END
With the start and end dates plugged into the formula: IF [Date Param] >=5/1/2016 AND [Date Param] < 7/1/2016 THEN 'Show' ELSE 'Hide' END
With the Date Param set to 5/1/2016: IF 5/1/2016 >=5/1/2016 AND 5/1/2016 < 7/1/2016 THEN 'Show' ELSE 'Hide' END will return true.
I noticed that the formula for step 3 in my first reply is incorrect. It should be IF [Date Param] >= [Start Date for Filter] AND [Date Param] < [End Date for Filter] THEN 'Show' ELSE 'Hide' END. One other thing is that when you drag the filter to filters the param will need to be set to a value that will return 'show' in order to set the filter to include 'show' So the parameter shouldn't be set to June when applying the filter as 'hide' will be the only value.
Let me know if you have any questions or issues.
Actors - Sample Mod.twbx 25.2 KB
Ivan, you are an absolute Tab-bro with Tableau!
I think your explanation above really helped my understanding too! Sweet lanta I hope I can one day answer one of your questions!
til then, live long and viz / prosper!