226|ALTERYX – Pay Gap Puzzle

[Marta_Ellsworth]

BYU Student Author: @Marta_Ellsworth
Reviewers: @Andrew_Wilson, @Sterling_Lane
Estimated Time to Solve: 30 Minutes

We provide the solution to this challenge using:

• Alteryx

Need a program? Click here.

Overview
Your consulting firm has been engaged by Orange County, Florida, to undertake a crucial analysis concerning the pay equity between male and female employees within the county’s workforce. In response to this important task, your supervisor has tasked you with preparing the foundational dataset that will serve as the cornerstone for this comprehensive analysis. You’ve chosen Alteryx as your primary tool to accomplish this task because of its power and efficiency for data preparation tasks. Your objective is to meticulously prepare and clean the data, ensuring it is ready for an in-depth analysis of the pay disparities that may exist. This initial step is critical and will set the stage for the subsequent analysis your team will conduct to uncover and address any issues of gender pay inequality that may exist within Orange County’s employment structure.

Instructions

1. Calculate the total average pay by gender:
   a. Add together the three pay columns (Base_Salary, Overtime_Pay, Longevity_Pay) then find the total average pay by gender.
   b. Expected columns: Gender, Avg_Total_Pay
2. Analyze pay differences across departments:
   a. Identify the difference in average total pay between male and female employees for each department.
   b. Only consider departments that employ both male and female employees.
   c. Organize the results to showcase departments with the largest pay differences at the top.
   d. Expected columns: Department, M_Avg_Total_Pay, F_Avg_Total_Pay, Difference
3. Analyze pay differences across divisions:
   a. Identify the difference in average total pay between male and female employees for each division.
   b. Only consider divisions that employ both male and female employees.
   c. Organize the results to showcase divisions with the largest pay differences at the top.
   d. Expected columns: Division, M_Avg_Total_Pay, F_Avg_Total_Pay, Difference

Data Files

• Challenge226_Pay_Gap_Puzzle.xlsx

Suggestions and Hints

• Before calculations, clean your dataset by converting blanks in numerical columns to zeros, ensuring accurate addition of the three pay columns.
• One way to solve this would be to start by segmenting your dataset based on gender. Next, calculate the average pay for each segment. Once you have these averages, reunite the segmented data by department or division.
• When merging the gender-segmented datasets back together based on department or division, utilize only the ‘Inner Join’ or ‘J’ output of the join tool. This ensures that you’re only including departments or divisions containing both male and female employees.

Solution

Challenge226_Solution.yxmd
Solution Video: Challenge 226|ALTERYX – Pay Gap Puzzle

Solution Images