Grouping dates of service to match a pay period

Isaiah02

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!

ColemenWilson

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!

ArborRose

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:

ColemenWilson

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!

ArborRose

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: