Calculate Number of Employees Over Time (using employee hire & termination dates)

Kutsch
Kutsch Member
in Cards, Dashboards, Stories

I have an employee data set with dates the employees were hired and then for some of them, when they were terminated- still unsure what to put in termination date column for employees that are still active.

I'd like to create a visualization that shows the number of active employees over time or at any given time.

I tried some stuff in beast mode, but wasn't quite getting it. In my head it seems like it should be easy, but I'm still struggling with it.

Any recommendations?

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I mocked up an example in the Domo Community instance using the Domo Dimension calendar dataset and the DomoStats People dataset as my "employee" table. The ETL looks like this.

    image.png

    The formula for the filter to actives tile is this:

    dt >= DateHired AND dt <= IFNULL(DateTerminated,CURRENT_DATE())

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    This is best done by adding a third date column by using the Domo Dimensions calendar dataset. You can filter it to pick one day out of each month and then create an IsActive column to by comparing the calendar date with the hire and termination dates. You could also do it for every day if you wanted daily granularity. You can apply the concepts in this video to help you.

    https://youtu.be/MgB__mhgrU4

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Kutsch
    Kutsch Member

    @MarkSnodgrass

    I'm a little confused on two parts:

    1. The filter rows: I'm a little unsure what exactly we're doing in this part. I have hire dates going back 50+ years and termination dates starting about 7 years ago. Do I need to filter to include these date ranges?
    2. I'm also unsure what to do for the Use Lead to Get End Date portion. I'm assuming this will be the 'IsActive' column. Which date should I run the function on?
    image.png

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I mocked up an example in the Domo Community instance using the Domo Dimension calendar dataset and the DomoStats People dataset as my "employee" table. The ETL looks like this.

    image.png

    The formula for the filter to actives tile is this:

    dt >= DateHired AND dt <= IFNULL(DateTerminated,CURRENT_DATE())

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories