4 Replies Latest reply on Sep 9, 2016 1:10 AM by Justine Dela Torre

    Monthly count of employees based on start and end date

    Chris Hatton

      I have a typical employee file/table with name, position, their start date and end date.  For active employees, the end date is null.  I am trying to create a tableau table where the rows represent each year and month, and a single column showing the number of active associates for each year and month.  How can do this based on the start and end dates of the associates, since dates between these 2 dates are implied.  Thanks

        • 1. Re: Monthly count of employees based on start and end date
          Steve Martin

          Hi Chris,

           

          I'm not sure I follow you: so your datasource has a start and end date but for active colleagues the end date is null but you need to show just the active colleagues?

           

          If this is correct then a simple calc of:

          The following will enable you to filter the set:

           

          If DateDiff('day',[Start Date],(If [End Date] Is Null Then [Start Date]-1 Else [End Date] End)) > 1 Then 'Out' Else 'In' End

           

          And this should allow you to count the number of active employees:

           

          Sum(

          If DateDiff('day',[Start Date],(If [End Date] Is Null Then [Start Date]-1 Else [End Date] End)) > 1 Then 0 Else 1 End

          )

           

          What I am doing here is if the end date is null, then assign the date value of the date before the start date

           

          Steve

          • 2. Re: Monthly count of employees based on start and end date
            Rody Zakovich

            Hello Chris,

             

            In order to show each year and month, along with how many active employees there were, you are going to have to do is create a Unified (Or Master) date column. That way you have a date field that is not dependent on your start or end dates.

             

            The easiest (And most flexible) way to do this is to create a table in your Database with a list of Dates, and then do a Cross Join to it. You can do this using Custom SQL, or a View, I prefer that latter if you are using SQL.

             

            Something like that....

             

            If you are using Excel, than please refer here

             

            http://kb.tableau.com/articles/knowledgebase/combining-start-end-dates-into-single-axis

             

            If neither of these method work, please let me know. There is a third method using Data Blending that would work as well. Though there are some limitations to that.

             

            Regards,

             

            Rody

            • 3. Re: Monthly count of employees based on start and end date
              Chris Hatton

              Thanks for the responses Steve and Rody, it jumpstarted my brain to understand what I need to do, with setting up a separate "Date Master" table at the granularity I need and drive the dates from there with the cross-join.

              • 4. Re: Monthly count of employees based on start and end date
                Justine Dela Torre

                Hi Rody,

                 

                I am having a similar problem and I saw your response on creating a View for an SQL data source. Can you elaborate on that? I have a similar, if not more complicated, problem as Chris. I have 1 data source and have created a master date file in Excel. I do need to display the number of actives and terminated employees based on the available hire and term dates, no status column for inactive or actives.

                 

                Hope you can help. Thank you!