1 Reply Latest reply on Nov 28, 2016 11:24 AM by Stephen Rizzo

    Start Date/End Date calculations

    Jon Leckman

      Hi all,

       

      I've been running in circles attempting to solve something that I'm sure has a simple solution but can't quite get to it.  I've been following the walkthrough regarding table calculations and dates here (kb.tableau.com/articles/knowledgebase/show-records-within-period-of-time) but to no avail.

       

      The short version:

       

      1)  I have a list of projects with start and end dates and expected workload required

      2)  I want to show a list of users with availability based upon how many projects they have assigned to them

      3)  The only graph needs to be DATE vs USER and to indicate if they have free time or not (and ideally which projects are taking it up)

       

      The problem I get to is when I go to graph I'm inevitably graphing "START DATE" vs "USER,"  I clearly need to refer to a calculated date field, but I'm not quite getting it.

       

      Can anyone point me at a good wakthrough?

       

       

      Thanks,

        • 1. Re: Start Date/End Date calculations
          Stephen Rizzo

          I don't know of a full walkthrough, but I can provide a few pointers about how I would go about putting this together. For starters lets assume that each project is assigned to an individual user. I would use a date table (in this case all you would need is a table with a continuous list of dates, so for the sake of simplicity lets assume that this table has a single field, [Date]) so that dates for which no project is starting or ending still show up in your chart. I would then left join that to your project data set (assuming a single row per project) using the conditions that [Date] >= [Project Start Date] and [Date] <= [Project End Date]. This will create a set of rows for each project such that there is one row for each day that project was active. You can then create a chart showing SUM([Number of Records]) by [Date] with a filter by [User] to show what you want.

          1 of 1 people found this helpful