4 Replies Latest reply on Mar 19, 2019 8:05 AM by Ewald Hofman

    Network Days Calculation with a Twist

    Jason Rowntree

      Hi All,

       

      I have tried searching other posts regarding my question but haven’t found a possible solution.

       

      In summary I’m trying to do a Network days’ calculation with a calculated field used to determine the end date.

       

      I have a join on 2 tables of data: -

      • Table 1 is used to bring in Employee name, Employee Number[Author], Org Structure and Employee Start date [Entry].
      • Table 2 is sourced from a separate system bringing in Employee Number and Transition dates (multiple records) determined by the Employee number.

       

      As Employees can go through multiple cycles of employment (contractors), we are trying to calculate after their most recent entry date how long it takes before they make a transition.

       

      The calculated field I currently have to work out the [Minimum transition date] during the most recent stint is:

      MIN(IF [Transition Date] > [Entry]THEN [Transition Date]end)

       

      The next calculated field I have is to work out the difference between the minimum transition date and the Entry date is:

      DATEDIFF('day',MIN({FIXED [Author]:MIN([Entry])}),[Min Transition Date])

       

      As I am fairly new with regards to these calculations I am currently at a loss as how to progress.

       

      Any help / suggestions would be greatly appreciated.

       

      Message was edited by: Jason Rowntree Sample Data included as a Packaged Workbook.

        • 1. Re: Network Days Calculation with a Twist
          Zhouyi Zhang

          Hi, Jason

           

          Can you provide a sample workbook?

           

          ZZ

          • 2. Re: Network Days Calculation with a Twist
            Ewald Hofman

            I think what you want is to add the FIXED LOD also to the minimum transition date. With a LOD you can calculate the aggregation for that dimension. In your case it will give you the minimum transition date per author. In the calculation you provided, you calculated the minimum transition date per row (and thus the value was always equal to the row value)

             

            But in your example, all transition dates only differ in time as far as I can tell. It would be nice if you provided clearer what the issue is.

            • 3. Re: Network Days Calculation with a Twist
              Jason Rowntree

              Hi Ewald Hofman

               

              Thank you for your assistance.

               

              Apologies that the sample workbook i created was a bit vague, it was difficult to create a subset and scrub all sensitive data and in my haste i didn't create enough variation in the transition dates field. The data set in reality is quite large and there will be a number of transition dates per employee (hence my creating a minimum transition date calculation which also has to verify if an employee is , the time component is irrelevant for calculations at this stage, only interested in days.

               

              In Theory I would envisage the calculation would be something as per below:-

              =Networkdays(Min(IF(Transition Date> Entry Date,Transition Date,Null)),Entry Date,Holidays)

               

              As I am still learning / understanding how to used LOD and the FIXED functions I am unsure how to use the correct syntax or breakdown the calculations in tableau.

               

              My thought process was to create the Min Transition Calculation which seems to be working fine, and then use that in a Date Diff calculation and strip out weekends. The Date Diff is working ( lag calculation) but i have no idea how to strip out the weekends.

               

              I have updated the workbook with greater variation in the transition dates.

              • 4. Re: Network Days Calculation with a Twist
                Ewald Hofman

                Thanks for the updated workbook.

                 

                Updating the expressions to calculate the Lag per author

                 

                Your calculation of Min Transition Date was not correct. It was calculating for each record the minimum transition date for in the context of that row. It would only return NULL if the entry was before the transition date, or the transition date else, as you can see in the image below.

                 

                The Lag was then calculating for each row the difference between the Entry of the author and the minimum transition date. You want it to be per author, and that is where LODs kick in.

                With an LOD you can define which level of detail you want to calculate the value for. Because you want to get the minimum transition date per author, you use the {FIXED [Author] : XXX } expression. For the XXX you define the calculation you want to use, which is in your case the MIN() function.

                 

                If you change the expression of Min Transition Date to

                {FIXED [Author] : MIN(IF [Transition Date] > [Entry] THEN [Transition Date] END)}

                then you will get this result

                 

                As you can see, you get the same value for Min Transition Date per author, no matter what the Transition Date on that row is.

                 

                The next step is to update the Lag function, which needs to find the Entry date per author again and compare it with the Minimum Transition date

                DATEDIFF('day',{FIXED [Author]:MIN([Entry]) }, [Min Transition Date])

                 

                Remove the weekends from the calculation

                 

                You are not the first to ask, and there are many good suggestions how to achieve this, including this article: DateDiff for Working days only