2 Replies Latest reply on Feb 7, 2013 9:22 AM by Michael Reilly

    Split Date Range from Source Data Into Days

    Michael Reilly
      I have an issue wherein my source data do not match my desired visualization structure.
      I have a number of events that occur with a start date, start time, end date, and end time, and I need to represent what proportion of each calendar day is spent in one of these events.  The source data contains one record per event, regardless of whether the event occurs in one day or spans multiple days.
      Actual Source Data Example
      1/1/20117:00 PM1/1/201111:59 PM
      2/28/20117:00 PM3/1/20112:00 AM
      3/15/20117:00 PM3/16/20112:00 AM
      5/15/20127:00 PM5/15/201211:59 PM
      5/16/20127:00 PM5/18/20122:00 AM
      Since I need to visualize the data by calendar days, I need a way to transform the source data.  When someone looks at my visualization, I would like for them to think that my source data looked like this:
      Desired Source Data Example
      1/1/20117:00 PM1/1/201111:59 PM
      2/28/20117:00 PM2/28/201111:59 PM
      3/1/201112:00 AM3/1/20112:00 AM
      3/15/20117:00 PM3/15/201111:59 PM
      3/16/201112:00 AM3/16/20112:00 AM
      5/15/20127:00 PM5/15/201211:59 PM
      5/16/20127:00 PM5/16/201211:59 PM
      5/17/201212:00 AM5/17/201211:59 PM
      5/18/201212:00 AM5/18/20122:00 AM
      I'm not sure how best to visualize the actual source data so that it looks like it came from the desired table.  Should I transform the data when accessing it from the data source?  Should I transform it within Tableau (after receiving my query results)?  I may or may not have the ability to influence the actual source data (i.e., by creating a new view on the data source), so if you think that is the best option, I'm happy to hear your idea, but I still would like to hear solutions that work from within Tableau.
      Thank you for any help you can offer!
        • 1. Re: Split Date Range from Source Data Into Days
          Dean Hewitt

          Hello Michael,


          Without understanding what type of analysis / visualizations you want to do it is difficult to help you.  I assume you have IDs for each of these events that are spread over several days. To best help you post examples of what you are trying to achieve. 



          • 2. Re: Split Date Range from Source Data Into Days
            Michael Reilly

            Thank you Dean,


            Unfortunately, I cannot share the data I am working with, but I will try to elaborate a bit.


            The underlying data do not have IDs - if I had to guess, I would say that the primary key is (START_DT, START_TM) since that is guaranteed to be unique.


            Let's call the time during which the event occurs "ON-TIME" and the rest of the time "OFF-TIME."  I need to show the breakdown of ON-TIME and OFF-TIME as a percentage of time in a given period with granularity down to calendar days (I don't think I'll ever need to look at hours or minutes).  So, I might have a viz that shows 10% ON-TIME and 90% OFF-TIME in 2012 and want to see that by month or by day.


            Since the data are broken down by events rather than days, multi-day events pose a problem.  If an event begins at 7:00PM on December 31st 2011 and ends at 2:00AM on January 1st 2012, I need the first 5 hours of that event to be aggregated in 12/31/11, 12/11, or 2011 while the last 2 hours of that event should be aggregated in 1/1/12, 1/12, or 2012.  As it stands, I do not have a clean way of breaking down events into the days that they span.


            Does that clarify the problem?



            - Michael