Joining Weekly And Monthly Data In ETL

JakeWright

Hello,

I have a data set that holds labor hours and wages for positions broken down by day of week. So for example, there are columns for Sunday through Saturday. Position 1 will have 0 hours in Sunday and Saturday columns and 8 hours in Monday through Friday columns. There is also a budgeted hourly wage. So what I can get is total hours * wage for budget per week. I want to see if I can find a way to join this to a dataset that is aggregated monthly.
I am aware I can figure out the number of days in a month and divide by 7 to get "Number of Weeks" in the month and then multiply that by total hours in a week. Unfortunately, since each position has varying budget hours on each day of the week, that wouldn't get me the exact number i'm looking for.

I'm currently brainstorming a solution for this, but wanted to go ahead and post here in case anyone already has a good solution.

Thanks in advance for any assistance offered!

MichelleH

@JakeWright Does your dataset include a date column for the first day of the week? If so, I would recommend using an Unpivot tile in MagicETL to turn your day of week columns into separate rows. Then you can use a formula tile to derive each individual date by adding a certain number of days to the Week Start Date based on the value in the Day of Week column you got from unpivoting. Once your data is in a daily format, then you can use Domo's native date functions to group those dates by Month.

GrantSmith

You can use the Date Dimension dataset and join your hours and wages dataset based on the day of the week, then you can use the year and month values in the date dimension dataset to aggregate on a monthly basis.

ArborRose

• Can you provide a small example simulating your data?
  
  Aggregate Daily Data to Monthly Level
  Use the transformation capabilities in Domo to aggregate the daily labor hours and wages data to the monthly level. This involves using the Group By function to group the data by month and then using the Sum function to sum up the total hours for each month. This step will give you a new dataset where each row represents aggregated data for a specific month.
  

• Join with Monthly Aggregated Set
  Once you have the monthly aggregated data from step 1, you can join this dataset with your existing monthly aggregated dataset. This join will allow you to combine the aggregated daily data with any other existing aggregated data you have, such as monthly sales or expenses.
  

• Add Calculated Fields
  After joining the datasets, you can add calculated fields as needed. For example, you can multiply the total hours by the budgeted hourly wage to calculate the total wages for the month.

MichelleH

@JakeWright Does your dataset include a date column for the first day of the week? If so, I would recommend using an Unpivot tile in MagicETL to turn your day of week columns into separate rows. Then you can use a formula tile to derive each individual date by adding a certain number of days to the Week Start Date based on the value in the Day of Week column you got from unpivoting. Once your data is in a daily format, then you can use Domo's native date functions to group those dates by Month.

GrantSmith

You can use the Date Dimension dataset and join your hours and wages dataset based on the day of the week, then you can use the year and month values in the date dimension dataset to aggregate on a monthly basis.

ArborRose

• Can you provide a small example simulating your data?
  
  Aggregate Daily Data to Monthly Level
  Use the transformation capabilities in Domo to aggregate the daily labor hours and wages data to the monthly level. This involves using the Group By function to group the data by month and then using the Sum function to sum up the total hours for each month. This step will give you a new dataset where each row represents aggregated data for a specific month.
  

• Join with Monthly Aggregated Set
  Once you have the monthly aggregated data from step 1, you can join this dataset with your existing monthly aggregated dataset. This join will allow you to combine the aggregated daily data with any other existing aggregated data you have, such as monthly sales or expenses.
  

• Add Calculated Fields
  After joining the datasets, you can add calculated fields as needed. For example, you can multiply the total hours by the budgeted hourly wage to calculate the total wages for the month.