You may know this already - but this is going to get more difficult before it gets easier.
My guess is that you need to cross-join the activity (join it back to itself), to effectively split out the activity into its individual days. Once you have it by day, then the rest should get easier again...
Basically, you need this >> FAQ: Open & Close Dates
Its not a specialty of mine, I avoid if I can
You may have to do this pre-Tableau (persoanlly I would use SQL), and then use Tableau to create the calendar.
Very best of luck!
Feel free to write back - if I cant help, we'll try and find someone who can
That is what I was afraid of. I was really hoping to find a way to do this once in Tableau, but after searching around, I'm not sure that is possible.
No need to change your structure except maybe adding an id to uniquely identify an event. In addition create a second table (or Excel sheet depending on your data source) containing a list of all dates (one record for each day).
Join both tables (sheets) where date >= start date and date <= end date. This will create a record for each event and each day it covers.
Use the date (not start or end date) as a filter in your report to determine the range to be displayed.
You might get something like this where each blue square represents an event within the specified timeframe. The same event will be repeated for each year, month, day where it is occurring.
I hope this helps.
I see how this could work instead, even though this would require us to continually update the second table, it might be a better option. I'm not understanding how you are suggesting to join the 2 tables though. I'm sorry, but can you explain this further?
3 of 3 people found this helpful
There is no need to continually update the second table. You could already create dates until one or more years in the future.
I suppose you are using Excel as a data source. Let me know if not.
In that case you will have one sheet (called Sheet1) containing the event data. A second sheet (called Sheet2) with one column. On the first row specify Date as header and place the dates on the following rows.
In Tableau connect to the Excel file but don't select Open. You need to select "Open with legacy connection" by clicking on the dropdown arrow of the Open button. This will allow you to create a so called "custom SQL" for Excel files.
Create "New Custom SQL". And specify the following syntax in the pop-up window :
from [Sheet1$] ,
where [Sheet2$].[Date] >= [Sheet1$].[Start Date]
[Sheet2$].[Date] <= [Sheet1$].[End Date]
It should look something like this :
Once this is done you can go to your sheet in Tableau and create earlier mentioned report (see screenshot above).
Let me know if you need more help. I will be glad to help
This worked perfectly! Thank you!