3 Replies Latest reply on Oct 4, 2012 6:45 AM by Jonathan Drummey

    Employee Counts

      I need to be able to determine the number of active employees each month, but the data availabe has only a start date for each employee and an end date if they have left.  The end date makes it easy to determine the number that left in each month. what I'm having a problem with is determining the number of active employees each month.  Have attached sample file.

        • 1. Re: Employee Counts
          Jonathan Drummey

          Hi,

           

          See the attached. This is a basic census problem, where you want to have a row for each transition (arrival/departure), and then you can use a running sum. I set that up using Custom SQL to create a union query in. There is still one issue to be dealt with, though, and that's when there's not at least one row for each month. You can see this in the "not padded line" worksheet in the attached, where there's no data for Feb 1 and the line connects Jan 1 to Mar 1. So it looks like there are 5.5 employees in February, when it should be 5. To get around that, I added a table of months and union'ed that in the Padded Custom SQL data source, and created views that work.

           

          Jonathan

          • 2. Re: Employee Counts
            Alex Kerin

            Nice job Jonathan. I see a utopia where perhaps we don't have to do any padding....

            • 3. Re: Employee Counts
              Jonathan Drummey

              Thanks, and nice to see you active again on the forums!

               

              I like your utopia, mine would have that too, along with a natural language interface to addressing and partitioning.