6 Replies Latest reply on Dec 4, 2015 12:38 PM by francesca.tangredi

    Calendar with Events Spanning Dates

    francesca.tangredi

      I am trying to create a calendar view so that we can filter on the project/event type, or department, or audience, etc. but I am having trouble with events that span dates. Some events are one day, which is simple, but others are two weeks, one month, three months, and so on. Because this is fairly new and there is not much data yet (about 30 rows in excel), I have the liberty of changing the structure of the data to whatever my needs might be. Once the Tableau calendar is created, others will be adding events to this regularly. I need events that span dates to show up as if they are happening in between the start and end date, but right now, I can only get a point to show at the start and end.

      We will also need to be able to filter on a date range and view any events happening during that range, even if the start or end date are outside of the range.

       

      Right now the data is set up as follows:

       

      Start_Date  /  End_Date  /  Event_Type  /  Department  /  Audience  /  Event_Title

       

      This structure can easily be changed, if there is a suggestion that would work better for my needs.

        • 1. Re: Calendar with Events Spanning Dates
          Mark Fraser

          Hi Francesca

           

          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

           

          Cheers

          Mark

          • 2. Re: Calendar with Events Spanning Dates
            francesca.tangredi

            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.

            • 3. Re: Calendar with Events Spanning Dates
              Erwin Ruttens

              Hi Francesca,

              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.

              events.PNG

              I hope this helps.

              Erwin

              • 4. Re: Calendar with Events Spanning Dates
                francesca.tangredi

                Hi Erwin,

                 

                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?

                 

                Thanks,

                Francesca

                • 5. Re: Calendar with Events Spanning Dates
                  Erwin Ruttens

                  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 :

                  select  *

                  from    [Sheet1$] ,

                          [Sheet2$] 

                  where   [Sheet2$].[Date] >= [Sheet1$].[Start Date]

                          and

                          [Sheet2$].[Date] <= [Sheet1$].[End Date]

                  It should look something like this :

                  events custom sql.PNG

                  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

                  3 of 3 people found this helpful
                  • 6. Re: Calendar with Events Spanning Dates
                    francesca.tangredi

                    This worked perfectly! Thank you!