5 Replies Latest reply on Sep 13, 2019 2:25 PM by Bryce Larsen

    Displaying Historical Inventory

    David Ames

      For each of my records, I have date fields that I will call [A] and [B]. These date fields indicate when certain events happened, which in turn indicates where in our process the work is currently being handled. This works very well to show current inventory and where that inventory is located. However, I have been asked to display that information historically. Specifically, what is desired is a graph which shows the date (generally just every day of the year, not connected to any record) in the column, and the volume of work that was in a particular area as of that date, in the row. So, for example, the row should show the number of records where [A] is earlier than the column date, but [B] is later than the column date.

       

      When searching for an answer to this, I found this article:

      https://kb.tableau.com/articles/howto/showing-records-that-fall-within-a-period-of-time

       

      I've attempted to follow the instructions for Option 2, but the result is not correct. It is possible that I just haven't implemented the instructions correctly, but I'm also not entirely sure whether this is the right solution to my problem. Can anyone confirm whether or not that article is the solution I need?

       

      Thanks in advance.

        • 1. Re: Displaying Historical Inventory
          Bryce Larsen

          Hi David,

          I was actually going to suggest Option 1: join to a master date table on the criteria you've mentioned.

          [Master Date] > [A]

          [Master Date] < [B]

           

          Would you be able to share a sample workbook that mimics your work?

           

          Alternatively, how are they selecting the day to view? If just using a date picker parameter, then you can write the filter to get the appropriate rows as well.

          • 2. Re: Displaying Historical Inventory
            David Ames

            The master date table option may be worth exploring. My data is in SQL and I'm an amateur in basically all aspects of databases, so I get a bit intimidated by table creation and modification. I've got a contractor I can consult for assistance on that though. I will look into it.

             

            Also, on further investigation of my problem with Option 2, I think I've found the problem. The issue is that date [B] isn't always populated, because if a case is still 'pending' in the middle phase the completion date hasn't been entered. So, a proper calculation of this work phase is when, on DATE, DATE was between [A] and [B], OR DATE was after [A] and [B] remains NULL. I think Option 2 from the link only does the former calculation and misses the latter.

             

            Will see what I can do on a sample workbook. Will take some effort to scrub my data as it is loaded with PII. For the date being selected, it would be a range. What is desired is to see changes in inventory over time, likely in the 1-2 year range mark. So, for example, I would want to display the historical inventory from 1/1/18 to the present.

            • 3. Re: Displaying Historical Inventory
              Bryce Larsen

              Gotcha. I think. Heh.

              Yeah, often times you can make a pseudo date placeholder that's just like IFNULL([B Date], TODAY()) to use as you only care about data through today anyway (or COALESCE([B], CAST(GETDATE() as date) in SQL).

               

              You could probably do this in SQL pretty easily before bringing into Tableau then.

              The tricky thing I didn't mention, however, is you probably want this as a left join, as you may want to include dates that don't have any records that were open on a day.

              Eg.

              Record 123, 1/1/2019, 1/5/2019

              Record 456, 1/7/2019, 1/16/2019

               

              Just joining on the logic I mentioned originally would not have 1/6/2019 in your data, so you may want to do it in SQL as a left join. OR you could look at using a date table as your primary data source and do some blending in Tableau, but I don't do that very often admittedly.

              • 4. Re: Displaying Historical Inventory
                David Ames

                I got the master date table added and then went to try Option 1 and it worked perfectly and much, much simpler than Option 2. Thanks for your help!

                1 of 1 people found this helpful
                • 5. Re: Displaying Historical Inventory
                  Bryce Larsen

                  Excellent! Glad it worked out for you! I had to do the same thing looking at ER Room Utilization per Hour - so definitely was an interesting challenge that you'll encounter again! Heh.