We provide the solution to this challenge using:
Need a program? Click here.
You have been tasked with assisting in an analysis for a subsidiary of your company, operating in the retail industry. As part of this analysis, you will evaluate unemployment levels in various geographic regions across the United States to assess how this economic factor might impact the company’s financial performance.
Knowing that manually collecting and analyzing this data would be a time-consuming and error-prone process, your boss recommended using a data visualization tool to create a dashboard that would streamline your analysis. Alteryx is the firm’s tool of choice to clean and prepare data for visualization since it maintains an audit trail. Despite preparing the data in Alteryx, your deliverable is an interactive dashboard hosted in Tableau. The dashboard will help the senior on your team use the charts in their own analysis.
The data used in your analysis are state unemployment levels and unemployment rates from FRED (Federal Reserve Economic Data). You are provided with five data files; four contain state unemployment levels for each US region, and the fifth contains unemployment rates for each state.
Section A – Alteryx
Load each of the datasets into a workflow to prepare these three output files:
- state_unemployment.csv – columns (Date, State, Region, Unemployed Persons, Unemployment Rate)
- region_unemployment.csv – columns (Date, Region, Unemployed Persons, Unemployment Rate)
- national_unemployment.csv – columns (Date, Unemployed Persons, Unemployment Rate)
The region associated with each state is in the datafile name.
Section A Hints
- Load each dataset into Alteryx, transform the data so it contains three columns (date, state, value).
- For each of the unemployment level datasets, add a column containing the region the state resides in (found in the original file name). Then concatenate each of the unemployment level datasets, so you have one dataset containing every region’s data.
- Join the dataset created in #2 with the transposed unemployment rate. The join values are date and state. The new dataset should have 5 columns (Date, State, Region, Unemployed Persons, Unemployment Rate). Output the joined dataset as “state_unemployment.csv”.
- Calculate the employment population (Unemployed Persons/(Unemployment Rate/100)) for each row, then summarize the data into two different sets: region data and national data. Using the employment population and unemployed persons calculate the unemployment rate for the region and national datasets.
- Output each dataset to a .csv file (“region_unemployment.csv” and “national_unemployment.csv”)
Section A Check Figures
Output check figures:
- state_unemployment.csv – 3,876 records
- region_unemployment.csv – 304 records
- national_unemployment.csv – 76 records
Section B – Tableau
Using the three datasets created in Section A, create a dashboard that visualizes:
- National and regional unemployment levels in time series format
- National and regional unemployment rates in time series format
- State unemployment rates and levels in a map and tabular format for a selected quarter
Feel free to create your own visualizations or recreate the dashboard pictured here:
Section B Hints
- Create a parameter named ‘State Displayed Value’ with the value options: ‘Unemployment Rate (%)’ and ‘Unemployed Persons’.
- Create a calculated field names ‘Parameter Value’ with the formula: IF [State Displayed Value] = “Unemployed Persons” THEN [Unemployed Persons] ELSE [Unemployment Rate] END.
- Use the field ‘Parameter Value’ as the measure in the map chart and include it in the detail of the state unemployment table, so it can be used to sort the table. Now, whatever value is selected within the parameter will be the measure displayed on the map.
Alteryx Workflow: Challenge50_Solution.yxzp
Alteryx Output Files: Challenge50_Alteryx_Output_Files.zip
Tableau Dashboard: Challenge50_Solution.twbx
Solution Video: Challenge 50|ALTERYX&TABLEAU – Unemployment Exploration