Grouping dates of service to match a pay period
I have two data sets one with employee pay information that is bi-weekly. And one that has information about the employee's work services they provided
The one with employee work data has dates that show when the service was performed (Date of service) and how many hours it took to do the service (Billable hours).
What I am trying to do is group up the dates of service so that they align with a pay period and compare the hours the employee was paid for vs the hours that were billed out to see productivity.
Thanks!
Best Answers
-
You can accomplish this in Magic ETL. You can use the Domo Dimensions calendar dataset and join that to your data which will then allow you to group your data by day, week, month, quarter, etc…
Then you can match up your periods for the billable hours vs hours paid.
If you are using custom periods that don't align with standard calendar weeks/months/quarters, you'll need to bring this data into Domo and use this for the groupings.
Let me know if you get stuck!
If I solved your problem, please select "yes" above
0 -
To do this in Domo, you'll need to join the two datasets and then perform some calculations to compare the billable hours against the hours paid during each bi-weekly pay period.
Employee Pay Information Dataset: Employee ID, Pay Period Start Date, Pay Period End Date, and Paid Hours.Employee Work Services Dataset: Employee ID, Date of Service, and Billable Hours.
Create a pay period table.Employee ID
Pay Period Start Date
Pay Period End Date
1
2024-06-01
2024-06-14
1
2024-06-15
2024-06-28
Join the pay information dataset with the work services.
Work Services Dataset
Pay Information Dataset
Employee ID
Employee ID
Date of Service
Date between Pay Period Start Date and Pay Period End Date
Join the Result with the Pay Period Table
Result from Previous Join
Pay Period Table
Employee ID
Employee ID
Date of Service
Date between Pay Period Start Date and Pay Period End Date
Aggregate Billable Hours - Group by Employee ID, Pay Period Start Date, and Pay Period End Date. Sum the Billable Hours.
Productivity Difference: Billable Hours - Paid Hours
In SQL, something like this:** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers
-
You can accomplish this in Magic ETL. You can use the Domo Dimensions calendar dataset and join that to your data which will then allow you to group your data by day, week, month, quarter, etc…
Then you can match up your periods for the billable hours vs hours paid.
If you are using custom periods that don't align with standard calendar weeks/months/quarters, you'll need to bring this data into Domo and use this for the groupings.
Let me know if you get stuck!
If I solved your problem, please select "yes" above
0 -
To do this in Domo, you'll need to join the two datasets and then perform some calculations to compare the billable hours against the hours paid during each bi-weekly pay period.
Employee Pay Information Dataset: Employee ID, Pay Period Start Date, Pay Period End Date, and Paid Hours.Employee Work Services Dataset: Employee ID, Date of Service, and Billable Hours.
Create a pay period table.Employee ID
Pay Period Start Date
Pay Period End Date
1
2024-06-01
2024-06-14
1
2024-06-15
2024-06-28
Join the pay information dataset with the work services.
Work Services Dataset
Pay Information Dataset
Employee ID
Employee ID
Date of Service
Date between Pay Period Start Date and Pay Period End Date
Join the Result with the Pay Period Table
Result from Previous Join
Pay Period Table
Employee ID
Employee ID
Date of Service
Date between Pay Period Start Date and Pay Period End Date
Aggregate Billable Hours - Group by Employee ID, Pay Period Start Date, and Pay Period End Date. Sum the Billable Hours.
Productivity Difference: Billable Hours - Paid Hours
In SQL, something like this:** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 98 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 713 Beast Mode
- 50 App Studio
- 39 Variables
- 669 Automate
- 170 Apps
- 447 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 104 Community Announcements
- 4.8K Archive