Grouping dates of service to match a pay period

Options

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

  • ColemenWilson
    edited July 8 Answer ✓
    Options

    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

  • ArborRose
    ArborRose Coach
    Answer ✓
    Options

    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! **

Answers

  • ColemenWilson
    edited July 8 Answer ✓
    Options

    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

  • ArborRose
    ArborRose Coach
    Answer ✓
    Options

    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! **