2 Replies Latest reply on May 6, 2016 4:29 PM by peter.hill

    Best way to handle dates, pivoting, and timeline data padding?


      Hey everyone,


      Thanks for anyone who can give me some ideas on ways to tackle this issue.  I have data describing manufacturing orders and each order is one line in a spreadsheet.  Tracking the timing of these orders is very important for our project management folks, and so I would like to put this data on a time scale to show the rise or fall in #/size of orders (demand) over a window of a few weeks into the future.  My goal is to visualize this as a weekday timeline so that the managers can see the upcoming workload visually and can quickly glance and determine if moving an order a day or two forward or backwards on the schedule would streamline the workload through the shop.


      The problem I am running into is our date fields.  Each order has a number of date fields: Date Received, Date Required, Cut Date, Load Date, etc.  This is how the shop manager enters the data, and this person is not a ninja with Excel or data manipulation.  I know that pivoting the data to where I have one "Date" field and an extra column "Milestone" to contain the [Received/Required/Cut/Load/etc] data would make my life a lot easier, but the problem is that I can't ask the shop manager to record the data in this way and the Smartsheet source this data lives in doesn't allow pivoting in Tableau.  Additionally, I am not a SQL user or very experienced in it (though I love learning new things).  The shop manager's process would be bogged down and get confused if I had him normalize the data and enter one new row per "Date/Milestone type" such as Cut Date, Load Date, etc.  Also, this data used to live in Excel, but now it lives in Smartsheets (which has some important limitations and distinctions from excel as it applies to pivoting dates, for example). 


      Example Real Life Situation:  Order #1 comes in on Monday, 5/2/2016.  It is required to be delivered (Required Date) on Monday, 5/9/16.  Due to the other orders in the queue, the shop finally starts building it (Cut Date) on Wednesday, 5/4/16 and loads it to be delivered (Load Date) on Friday, 5/6/16.  The order was for 500 pieces, and we do not work weekends.  The production manager currently estimates an average number of pieces in the queue per day based on the size of the order, the day it was received, and the Lead time the shop was given to complete it.  So if the shop was given 5 days to build an order, and the order was for 500 pieces, they would like to estimate approximately 100 pieces per day (PCS/Order Length in Days) between the Received Date and the Required Date.  This we'll call the "Daily Piece Quota." 


      Desired Visualization:

      I would like to show time on the X axis (like Week 29 M-F, Week 30 M-F, Week 31 M-F, etc) and "Piece Quota Per Day" on the Y-axis.  Each day would show a sum of all order quotas that need to be touched that day in order to finish by the required date.  So if a 35-piece order is received on Monday and Due Friday, the "Daily Piece Quota" should be 7 (7 pcs x 5 days = 35) pieces and should show up on every weekday M-F, even though the only dates associated with that order are the Monday and the Friday, not the dates in between.  



      Note: in the attached example, the two tabs should help give you an idea of what I want the end result to look like.  Take Order ID #13 for instance.  On the Dual Axis tab, it only displays the daily piece quota on the two dates that are associated with that record.  How do I get it to add that "Daily Piece Quota" value to each day in between the [Received Date] and the [Required Date]?