1 Reply Latest reply on Jul 19, 2013 12:22 PM by Mark Holtz

    Finding data points between two date demensions

    Josh Price

      I manage a large fleet of railcars and it would help a great deal if I could graph the amount of railcars that were at transload sites over a given date range.  It seemed like a relatively easy metric to graph but it's proved very challenging in Tableau (for me at least).

       

      My data shows when each car arrived and departed the terminal.  I need to be able to calculate how many cars were on-site per day using these given dates.

       

      Examples:

       

      Car A

      Arrival - 5/1/2013

      -

      -

      -

      -

      -

      Departure - 5/7/2013

       

      Car B

      Arrival 5/5/2013

      -

      -

      Departure 5/8/2013

       

      Based on the two cars above, the graph should show.  I will also need to have railcars split by material.

       

      5/1/20135/2/20135/3/20135/4/20135/5/20135/6/20135/7/20135/8/2013
      11112221

       

      I've played around with DATEDIFF() but simply taking the difference of the start and end date, and then adding it to the start date does not give me the desired result.  I attached an example file that shows my way is only graphing the car at the end date, when I need to carry the car count accross the entire graph.

        • 1. Re: Finding data points between two date demensions
          Mark Holtz

          Hi Josh,

           

          Since no one has responded to you, I thought I'd at least share my thoughts these 2 months later!

           

          I would call what you're trying to create an "inventory view." Items enter "inventory" on your Arrival Date and exit on your Departure Date.  As you outlined, you unfortunately do not have all the records for your Car A and Car B.

           

          I would recommend that you create the data outside Tableau rather than trying to use Tableau to accomplish this. I find the Tableau solutions incredibly complex, whereas the creation of the data you need externally really isn't so bad. The concept of "filling" the values between those two endpoints is called Domain Padding, Domain Densification, or Domain Completion here on the Tableau forums.

           

          Jonathan Drummey has shared wonderful insights on his Drawing with Numbers blog: domain completion | Tableau Wiki | Drawing with Numbers

           

          If you're at a loss as to how to build the data externally, you could do it with custom SQL. You'd need a source that holds all time intervals (a Date dimension table from a data warehouse is a good source of this info, or even just creating a spreadsheet with the list of dates and pulling it up into your database). You can then CROSS JOIN all car records to all date records to get every possibility. Then, create a case logic to provide a 1/0 indicator field for Arrivals, Departures and InInventory (or you can call it InTerminal to use your parlance) for each day. You can throw out records with all 0's if you want to trim down the size of this data source.

           

          Here is a representation of the two cars you provided. You can see it explodes the data quite a bit, especially if you're covering a long date range. This also permits units to re-enter inventory (Car A might return on 5/10/13, in which case it would show another 1 in Arrivals).

          DateCarArrivals Departures InInventory
          5/1/2013A101
          5/2/2013A001
          5/3/2013A001
          5/4/2013A001
          5/5/2013A001
          5/6/2013A001
          5/7/2013A010
          5/8/2013A000
          5/1/2013B000
          5/2/2013B000
          5/3/2013B000
          5/4/2013B000
          5/5/2013B101
          5/6/2013B001
          5/7/2013B001
          5/8/2013B010

           

          When you pull this into Tableau, you'll be able to graph the InInventory field very easily, and you can also show arrivals and departures.

           

          Hopefully that is useful to you, even this much later.