HR-Data-Analysis-Assessment

Table Of Contents

• Introduction
• Datasets
• ad_hoc_requests

Introduction

Hello everyone! I am thrilled to announce that I have secured a Data Analyst Internship at Psyliq. I’ve been tasked with an exciting challenge — an HR Data Analysis Assessment comprising 17 questions.

Datasets

employee_survey_data: It is the dimension table contains information related to Environment Satisfaction, Job Satisfaction, and work life balance.
general_data: It is the fact table contails all the details of Employee
in_time: It is the dimension table contains all the details time
out_time: It is the dimension table contains all the details time

ad_hoc_request

1. Using Excel, how would you filter the dataset to only show employees aged 30 and above?
2. Create a pivot table to summarize the average Monthly Income by Job Role.
3. Apply conditional formatting to highlight employees with Monthly Income above the company's average income.
4. Create a bar chart in Excel to visualize the distribution of employee ages.
5. Identify and clean any missing or inconsistent data in the "Department" column.
6. In Power BI, establish a relationship between the "EmployeeID" in the employee data and the "EmployeeID" in the time tracking data.
7. Using DAX, create a calculated column that calculates the average years an employee has spent with their current manager.
8. Using Excel, create a pivot table that displays the count of employees in each Marital Status category, segmented by Department.
9. Apply conditional formatting to highlight employees with both above-average Monthly Income and above-average Job Satisfaction. 10.In Power BI, create a line chart that visualizes the trend of Employee Attrition over the years.
10. Describe how you would create a star schema for this dataset, explaining the benefits of doing so.
11. Using DAX, calculate the rolling 3-month average of Monthly Income for each employee.
12. Create a hierarchy in Power BI that allows users to drill down from Department to Job Role to further narrow their analysis.
13. How can you set up parameterized queries in Power BI to allow users to filter data based on the Distance from Home column?
14. In Excel, calculate the total Monthly Income for each Department, considering only the employees with a Job Level greater than or equal to 3.
15. Explain how to perform a What-If analysis in Excel to understand the impact of a 10% increase in Percent Salary Hike on Monthly Income.
16. Verify if the data adheres to a predefined schema. What actions would you take if you find inconsistencies?

Click here for solution :Here