5 Replies Latest reply on Nov 29, 2018 12:42 PM by Raza Asghar

    How to divide hours across a multi-day task?

    Raza Asghar

      Hi,

       

      I am trying to create a Resource Management view in Tableau which will allow me to see how many hours of work need to be dedicated by each owner (IT, Ops) across several days. My data source has 5 rows, each representing a task. Each task has an (1) Owner, (2) Target Start Date, (3) Target End Date, (4) Duration of Task in days (including both bounds, meaning a task that has a Target Start Date of today and a Target End Date of tomorrow would have a duration of 2 since it would be completed on the first day, today, and the second day, tomorrow), and (5) Total Target Hours, which is the total number of hours it takes to complete the task, across all days.

       

      In the Tableau packaged workbook I have attached, I have 2 tabs. Both tabs have the Target Start Date (exact date) on the Column and the Owner on the rows. I am trying to show the # of hours each Owner has to dedicate per day to complete the tasks.

       

      For single-day tasks, like Task #1 (refer to attached Excel data source), the Total Target Hours is 3. Since that task has the same Target Start and End Date of 1/16/19, it is clear that the Owner, IT in this case, would spend 3 hours on that task for that day. However, I am having trouble dividing up the hours for multi-day tasks. If I stick with the 1st view in Tableau, in the tab called "Daily OverStated", which sums up Total Target Hours in the table view, you can see that it is showing that the IT Owner spending 10 hours on Mon 1/14. This is wrong since it is assuming Task #5, which takes 10 hours to complete across 5 days, will all happen on 1/14. This is of course because I have Target Start Date as the x-axis. The same thing would happen if I had Target End Date, it would just show the 10 under the End Date.

       

      If you go to the 2nd view in Tableau, in the tab called "Daily UnderStated", I created a calculated field which divides the Total Target Hours by the Duration of Task. The issue with this view is that it will accurately show the hours needed to complete tasks by owner for each Start Date, but will forget about the remaining hours needed to complete the task... Once again, because I have Target Start Date on the x-axis. For example, for that same Task #5, it accurately shows that IT will only need to spend a total of 2 hours on Mon, 1/14. However, it does not show the remaining 8 hours (2 hrs/day) that would need to be spent by IT on Tues, Wed, Thurs, & Fri

       

      The view I am TRYING to get to is as pictured below (this accurately spreads the 2 hours/day that IT will spend on Task #5 across the 5 days, as well accurately divides the 1.5 hours/day that IT will spend on Task #2 across Wednesday and Thursday):

      Any idea what I can do to have Tableau show this correctly? If there is NO way in Tableau, I am open to suggestions to manipulating my data within the Excel data source to get to the correct view in Tableau. Thank you, I will answer any clarification questions - I know this was quite the lengthy post. Note: please ignore the dates in the Excel Data Source with no task information associated with it - that's just so I can have all the days show up in my Tableau tab views... A weird workaround I know.

        • 1. Re: How to divide hours across a multi-day task?
          Shinichiro Murakami

          Not exactly sure, but hope this helps.

           

          From edit data source , pivot data of two dates.

           

           

           

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: How to divide hours across a multi-day task?
            Chris Chalmers

            Hey Raza,

             

            Your detailed write-up made it easy to understand what you're trying to do, thank you for that.

             

            What you're looking for is Data Densification. Tableau normally only shows at most one mark per row of data. You want your viz to show more marks than rows; if you have a row that specifies a 5-day task, you want five marks to appear for that task. Data Densification allows you to show more marks on the viz than there are rows in the data.

             

            Step 1: Pivot

            Pivot on Target End Date and Target Start Date. For convenience, I rename the pivoted columns to Date and Date Type. We do this to merge the date fields so that we can put start and end dates on the same axis.

             

            Pivot.PNG

             

            Step 2: Adjust End Dates

            For this strategy to work, you need the end date to be exclusive, not inclusive. Create an adjusted date calculation:

             

            AdjustedDate.PNG

             

            Step 3: Densify

            Put [Adjusted Date] on Columns and [Owner] on rows. Filter as desired. Right-click on the [Adjusted Date] pill and select "Show Missing Values". You should see something like this:

             

            AfterDensification.PNG

             

            What Tableau has done here is subtle but important. There are text marks for every owner/date combination, even if there aren't rows in the data for those combinations. Table Calculations can now act on those marks as if they are rows of data.

             

            Step 4: Write a Table Calc

            TableCalc.PNG

            This calculation walks from date to date, remembering the previous value and adding/subtracting from it. When it reaches a date that is a start date, it adds the hours per day associated with that task. When it reaches a data that is an end date (our adjusted, exclusive end date), it subtracts the hours per day associated with the task. If there are multiple tasks starting/ending for one owner on the same day, it sums the contributions of each task before adding the result to the running sum.

             

            Step 5: Show It

            Drag the table calc to the Text shelf, and make sure it is calculating across the table. You should see something like this:

             

            Viz.PNG

             

            I created an intermediate calculation to make the zeroes disappear like in your desired image. Take a look at it in the attached workbook.

             

            Hope that helps!

            -Chris Chalmers

            • 3. Re: How to divide hours across a multi-day task?
              Raza Asghar

              Wow, thank you, this was super helpful! I have a follow up question:

               

              How can I make it so that for multi-day tasks that roll over the weekend, to have the hours skip those weekend days and presume on the beginning working day of the next week (Monday). So for example, if a task starts today, 11/29, and rolls over into 12/4, next tuesday, and has Total Target Hours of 8, how can I have it skip the Saturday (12/1) and Sunday (12/2) from allocating hours to those days?

               

              Meaning the breakdown for hours per day would look as follows:

               

              Owner
              11/2911/3012/112/212/312/4
              IT2222

               

              Rather than

               

              Owner
              11/2911/3012/112/212/312/4
              IT2222

               

              Note: my Target Day Duration field is based on the Excel function =NETWORKDAYS, which only counts business days M-F as counting. Thank you.

              • 4. Re: How to divide hours across a multi-day task?
                Chris Chalmers

                Hey Raza,

                 

                Since your duration field already accounts for weekends, all you have to do is skip over the weekends in the Table Calculation. Everything with table calcs is a little more complicated than with normal calcs, but this seems to work:

                WeekdayLookup.PNGTableCalcNoWeekends.PNG

                 

                I feel like there's probably an easier way to do the weekday lookup, but I kept running into problems with other approaches.

                 

                -Chris Chalmers

                1 of 1 people found this helpful
                • 5. Re: How to divide hours across a multi-day task?
                  Raza Asghar

                  Thank you once again! You are astounding at this - I don't fully understand everything, but as you mentioned, table calcs are a bit more complicated. I don't mean to ask too much but can this be extrapolated out to get a week view of everything?

                   

                  Meaning, if I wanted to see the week view by owner, with the most recent task I mentioned in my previous post, it would show as below:

                  Since 2 hours were completed on 11/29 and 11/30 each, which is the week of 11/25, and 2 hours were completed on 12/3 and 12/4 each, which is the week of 12/2.

                   

                  I presume I would need to make a new Adjusted Date as well as a new table calculation since the previous exercise was all based on a day by day basis.  Is there a way to do this weekly while also excluding weekends from being days that Owners can work on? Thank you!