Calculate Number of Employees Over Time (using employee hire & termination dates)
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?
Best 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.
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
I'm a little confused on two parts:
- 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?
- 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?
0 -
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())
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive