5 Replies Latest reply on Jul 19, 2018 12:53 PM by Yuriy Fal

    Average Headcount using multiple effective dates

    Patrick To

      I have combed through the forums and have tried many of the solutions out there and seem to always end up hitting a road block. I am using Tableau 10.5 Desktop.

       

      This is what I am attempting to accomplish:

       

      - Calculate Average Headcount over a user-defined period of fiscal years (note: start of fiscal year is March).  I am not opposed to using parameters to pass in start and end years to define the period of time in question.

      - I am required to calculate additional metrics that will leverage the Average Headcount calcs above. As an example, I would also be required to calculate 'Promotion Rate', which would simply be (# of Promotions for fiscal year i / average headcount for fiscal year i).  So example, FY 2010 Promotion Rate = X; FY 2011 Promotion Rate = Y; FY 2012 Promotion Rate = Z... and so on.

       

      Additional data info (see attached sample spreadsheet):

       

      Each employee has a unique [Employee Number].  For each Employee, every event, such as promotion, job title change, transfer, rehire, termination(s) etc get captured and an [Effective Date] (when this event becomes effective) gets labelled with each event.  Along with each event, the employee gets a [Employee Status] assigned (Active, Terminated).  Further, the system creates a date/time stamp [EjhDateTimeCreated] when that event gets created within the system.  Unfortunately, for some Employees, they have multiple events that occur on the same day (date/time is unreliable here for sequencing), so an assumption is made that the record with the max. system created date (for each Effective Date) should be taken as the last event to occur on said day.

       

      Keep in mind:

      - each employee has gaps in their effective dates records (ie. they do not have an event that takes place every fiscal year)

      - while I have an Original Hire Date and Termination date, employees can and do quit and come back - thus, multiple termination dates for some employees.

      - i have about 2500 employees each who have a # of effective dates (anywhere between 1 and 20), so cross joins may be impractical.

       

      Please see attached sample.  Any help is much appreciated.  Thanks.

        • 1. Re: Average Headcount using multiple effective dates
          Yuriy Fal

          Hi Patrick,

           

          This is a common data transformation (re-shaping) task,

          when you have a table of transactions (state change events),

          but you'd like to have a (daily) periodic snapshot table

          to track / analyse states across time dimension.

           

          Such a transformation task is a natural fit for Tableau Prep,

          but it could be made right in Tableau Desktop --

          using a Range Join with the (daily) Calendar table.

           

          Please find the attached as an example.

          Hope it could help a bit.

           

          Yours,

          Yuri

          • 2. Re: Average Headcount using multiple effective dates
            Patrick To

            Hello Yuriy,

             

            Thank you for your response.  This is similar to a solution that I had worked out earlier (using a 1:1 cross join from a similar Calendar table).  The '<=' join filter is, however, a nice addition that I never thought to include. 

             

            Unfortunately, as stated in my last point above, I am looking for alternative solutions (if they exist) to the cross-join since I have approx. ~2500 employees (and growing) with # of Effective Dates per employee ranging from ~1-20, so performance may eventually become an issue.

             

            Either way, I appreciate the input Yuriy.

            1 of 1 people found this helpful
            • 3. Re: Average Headcount using multiple effective dates
              Yuriy Fal

              ... I am looking for alternative solutions (if they exist) to the cross-join ...

              That's why I've mentioned Tableau Prep.

              With Prep one could do a transformation just once,

              get output as an extract or CSV file,

              and do the rest in Tableau Desktop.

              1 of 1 people found this helpful
              • 4. Re: Average Headcount using multiple effective dates
                Patrick To

                If it differs from the range/cross join solution that we've discussed, could you please elaborate on the 'transformation' that you would impart on the data (within Tableau Prep) in order to get it into a usable form ?

                 

                Thank you.

                • 5. Re: Average Headcount using multiple effective dates
                  Yuriy Fal

                  Hi Patrick,

                   

                  The approach with Prep is almost the same, please take a look:

                  Headcount_YF.png

                   

                  The combination of the Aggregate 1 + Join 1 flow steps is equivalent to

                  the [YF : Last Event per Day] FIXED LOD based calculation (used as a filter),

                  same with the Aggregate 2 + Join 3 and the [YF : Event Filter] one.

                   

                  The Join 2 is the same as the Datasource (non-equi) Join.

                   

                  The final output is the same as the workbook datasource

                  with the two [YF : ...] filters applied (47395 rows).

                   

                  Please find the attached Prep Flow

                  (packaged with the source Excel file).

                   

                  Yours,

                  Yuri