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

Kutsch

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?

MarkSnodgrass

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.

The formula for the filter to actives tile is this:

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

MarkSnodgrass

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

Kutsch

@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?

MarkSnodgrass

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.

The formula for the filter to actives tile is this:

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