2 Replies Latest reply on Dec 3, 2014 8:47 PM by Syed Khadir Ahmed

    Extrapolating Dates based on Start date and End date

    Syed Khadir Ahmed

      Hi All,

       

      I have a start date and end date based on that I am trying to extrapolate the dates which falls between those two dates.

       

      Example: Start date is 1st Jan'14 and End date is 31st Jan'14.

       

      1. I wanted to show a day on day information for Multiple associates

      2. Mark different color for a day if its a holiday.

      3. That date should automatically show the information only for next 3 weeks.

       

      I can say that I am looking for a Gantt Chart with day on day info.

       

      Thank you in advance attached is the sample spreadsheet.

       

      Please note: I am learning the software and this is just the real time scenarios I face wanted to deal it in tableau.

       

      Regards,

      Syed

        • 1. Re: Extrapolating Dates based on Start date and End date
          Patrick Van Der Hyde

          Hello Syed Ahmed,

           

          This question is a couple weeks old but I wanted to see if you found a solution.

           

          If the data was structured differently so that the holidays and data are in the same file (or joined to create a single data source) then the desired view could be created with a few calculated fields.

           

          see the attached workbook with the example of this in use.

           

          I realized that I created this in version 8.3 which was released this week.  You can download this version from here if you have not already. 

           

          I have modified the data source to include a holiday line for each employee Name/Holiday combination.  Then it is possible to to create a line per "Name" and limit the size of this line to start and end on Today and today+3 months using calculation functions for Datediff and Dateadd.

           

          [Start Date New] is calculated as:

           

          Case [Type]

          When "Work" then

          If [Start Date]>today() then [Start Date] else today() End

          When "Holiday" then

          If [Start Date]<today() then null else [Start Date] end

          End

           

          and the number of days to 3 months from now is calculated as:

           

          datediff('day',today(),dateadd('month',3,today()))

           

          Finally, the size of the line is calculated to only show the next three months as a max value for each line: 

           

          Iif(datediff('day',[Start Date],[End Date])>[days to three months from now],[days to three months from now],datediff('day',[Start Date],[End Date]))

           

          The Holidays are demarked by color in the view and appear as Blue as long as the color legend has "Holiday" appear above "Work".

           

          I hope this helps provide some ideas for you about the things you can do with Tableau and ways to think about working with Data.

           

          -- Patrick

          • 2. Re: Extrapolating Dates based on Start date and End date
            Syed Khadir Ahmed

            Thank you so much Pat, This is really helpful.

             

            Is there a way we can have some kind of join to do this, Because I have huge data and will be difficult to populate in one single sheet along with Holidays.

             

            Based on this two tables if we can come up with on Table which gives 1 entry for each day for each employe and resource type. :-)

             

            (Not sure if SQL helps .. Trying that as well.. :-)

             

            Thank you!!)