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.