1 Reply Latest reply on Nov 12, 2018 9:19 PM by swaroop.gantela

    Question Calculating Days and Days Off to a Start Date to Obtain the Proper End Date

    Evelyn Trimble

      Hi,

       

      I am trying to solve an issue I am having with adding days and non-worked days to a start date in Tableau 10.5.

       

      I have the start date and total number of days required and now i just need to calculate the days that are non-work days by adding a value of 1 to each of them so that I can push out the end date of the test period out.  (Basically if my start day is November 21, 2018 and I am going to require 10 days which brings me to December 1, 2018 I would like to include all of my non worked days so that my end date would be December 10, 2018--I have 2 days off for holiday days and three weekends to add to this time period.)

       

      I have an excel spreadsheet that has all of the non-worked days included in them (holidays, weekends, every other Friday).

       

      Can anyone help me with this calculation?

       

      Thanks,

      ET

        • 1. Re: Question Calculating Days and Days Off to a Start Date to Obtain the Proper End Date
          swaroop.gantela

          Evelyn,

           

          I'm not sure that I quite got there, but maybe this can give ideas.

           

          At the outset, I'll say that it is not very efficient as it involves a crossjoin

          with a data-scaffold of all dates. But this can likely be optimized, especially in Tableau Prep.

           

          The idea is to have the start and required days in one table, the nonworking days in another,

          and a scaffold of all days as a third table. The joins of this are shown in the screenshot below.

          (This can be improved upon in Prep to only join those scaffold dates greater than start date).

           

          Then there is a workingdayFlag:

          IF ISNULL([Non Work])  THEN 1 ELSE 0 END

           

          Then start adding up working days

          RUNNING_SUM ( SUM ( [DayStatusFlag] ) ) - 1    // note:  -1 may not be necessary

           

          (the -1 was to match your number, but I may have it wrong as to which Friday is off)

           

          Then the End Date is:

          WINDOW_MAX(   

          IF ATTR([Days Required])=[RunningSumWorkDays]

          THEN ATTR([Scaffold Date])

          END)

           

          This is all highly contingent upon the table calculation settings, screenshot below.

           

          Finally, there are a few filters to clean things up.

           

          Please see workbook v10.3 attached in the Forum Thread:

          Question Calculating Days and Days Off to a Start Date to Obtain the Proper End Date

           

          287281schedJoin.png

           

          287281schedTCset.png