How to Calculate Headcount dynamically from Employees Transaction Records

Many business dashboards track headcount figures, since it is quite a vital indicator for most organizations. Conceptually, headcount is a straight forward concept. Technically, it is actually very complex to render for analysis, as it should not be stored, but calculated for a set of circumstances: Time, employee role, location, gender, …

Headcount is indeed very close technically and conceptually to inventory management, with flows of employees coming in and out the organization, and the required snapshot of where it stands. Headcount computing can be also compared to the approach needed for ROI calculation, as addressed in one of my previous posts. At a higher level of abstraction, a very good read by Keith Helfrich is diving into the similarity of those data sets. However, whereas Keith is using Custom SQL and Table Calcs in Tableau to reshape the data, I will take the Alteryx route, which I find cleaner, easier to explain and document.

I can get to this very dynamic way to report on headcount:

 

Now, how to get there? Let’s start with the source data: a set of transactions records for each employee, that you can download from here, looks like this:
Data Source Format

We need to reshape that data before we load it into Tableau, so that we can count at any point of time how many employees are active in the headcount:
Headcount workflowThe approach consists in processing the hires as positive additions to the headcount using these formulae for the inflow (=hires):
Flow In
and creating additional new entries for the outflow (=terminations) of employees which will decrease the headcount only after the terminations occur:
Flow Out

After running the workflows, the data will look like this:
Proof DataNote how the business date is using hire date for inflow, and termination date for outflow. Furthermore, the granularity of the transactions increases:
Headcount workflow 2I start with 110 employee records, and add 22 terminations to end at 132 rows loaded into Tableau.

In Tableau, I need a couple of calculated fields to get the dynamic headcount and various additional relevant indicators:
Headcount = Running_Sum(sum([Flow]))
Hires/Arrivals = if [Flow Type]=’In’ then [Flow] END
Terminations/Departures = if [Flow Type]=’Out’ then -[Flow] END
Departures Voluntary = if [Flow Type]=’Out’ and [Termination Type]=’Voluntary’ then -[Flow] END
Turnover = sum([Departures])/[Headcount]
Tenure in Days = DATEDIFF(‘day’,[Hire Date],iif(isnull([Termination Date]),today(),[Termination Date]))/365
Tenure in Years = DATEDIFF(‘year’,[Hire Date],today())
Age = DATEDIFF(‘year’,[Birth Date],today())

Now there is an important additional modeling challenge to address: headcount being a Running Sum table calculation, it needs the full history of employee records since the beginning of the organization, or at least a starting point, which is most likely further in the future that your visualization will require. In other words, if I am reporting on Microsoft headcount, I do not necessarily want to display the headcount all the way back to 1975, the last 2 or 3 years are usually what is relevant to display. The challenge is that if I add a filter on Business Date for the last 3 years, my headcount will be wrong, counting only the flows happening over the last 3 years. Hence the need to create in Tableau an extra Reporting Date Filter filtering the Table Calcs without filtering Business Date:
Reporting FilterNow if you have been following my posts, we are entering with this filter what I have been calling Tableau post wall territory in terms of complexity, so buckle up!
First things first, it is important to understand in which order the Tableau engine performs calculations. As Bora Beran explained in his blog, Table Calcs like Running_Sum are performed last:
Calcs OrderThat works well for our purpose, now which formula to use? That good old Lookup() Jedi Trick:
Reporting Date Filter = lookup(max(([Business Date])),0)

This is simple to implement, and will work really well for most purpose, like on the Headcount or the Headcount progress tabs above, because the data underlying is dense enough, that is you have at least one entry per business date month. However, should you need to display a data set not dense enough, such as headcount per location per quarter, as illustrated by the Locations Wrong tab above, all hell breaks lose, and instead of getting this:
Locations RightYou are instead getting this:
Locations WrongIt took none other than Joe Mako himself to help me find the proper formula that does address this data densification issue:
Reporting Quarter Filter = DATE(DATEADD(‘quarter’,INDEX()-1,DATETRUNC(‘quarter’,#First business date in your dataset#)))

Make sure you check Show Missing Values from the Quarter drop down options. If you want to push further your understanding of data densification issues, beyond the purpose of this blog post and at the frontier of my abilities, I highly recommend this material put together by Joe, in Jedi Master territory. Thanks again Joe!

Here are the links to the Tableau Viz, the source data, the proof data, and the workflow used in this demo.

This entry was posted in Alteryx, Tableau and tagged , , , . Bookmark the permalink.

10 Responses to How to Calculate Headcount dynamically from Employees Transaction Records

  1. Ubair Usmani says:

    Hi,

    Kindly advice how you have calculated “Flow” fields. I am not able to identify the tool in above snapshot.

    Thanks

  2. I am trying to recreate the headcount tab and getting an error message when I drag the “Reporting Date Filter” into the filters area and add the last 3 year filter. Error message, “The calculation ‘reporting date filter’ can’t be applied to a user defined aggregate.” Please send me more detailed instructions.

    Thank you

    • Frederic says:

      Hi,
      Have you downloaded the sample files and followed each step of the way? You must have missed a step, and without seeing your Tableau workbook, it is hard to identify which…

    • Matt says:

      Happened to me as well. Let me know if you figured out how to solve the issue

  3. Kürşat says:

    Hi, Frederic
    Great post.
    I’ve tried to copy the way you did in your post for my headcount project.
    I’ve encountered some problems.
    When I try to show last three years’ gender evo with bar chart (color by gender), male values disappear in the years which don’t have any male flows and total headcount shows wrong.
    However, when I try to show last three years’ gender evo with bar chart(not colored by gender), total headcount shows true.
    What may be the problem?How can I show the true gender distribution with true headcount number?
    Thanks in advance.

    • Frederic says:

      Hi Kursat,

      You are facing the data densification problem I address at the bottom of that very post. make sure you read in details the part that starts with: “Now if you have been following my posts, we are entering with this filter what I have been calling Tableau post wall territory in terms of complexity”. You will see that you need to use a dedicated reporting quarter. You can also download the workbook I posted on Tableau public to see the difference with that view by Gender called Gender Evo…

      • Kürşat says:

        Thanks,
        I missed it. I solved the problem.
        I want to ask you how can I show them in the dashboard using the reporting date filter. I could not make it global filter. How can I achieve this?
        Thanks in advance.

  4. Joe Winkler says:

    Great article! I’m finding, however, that the data isn’t computing properly. We actually have 792 active employees right now but it’s showing 722. When I add up the “headcount” column, that results into 722, but when I run a report that shows a roster of active employees and I filter out anyone with a termination date, it shows me 792 employees. I’m not able to ascertain why I’m off by 70 employees. I’d love to send you the workbook to see what I’m talking about if you could help me. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *