50|ALTERYX&TABLEAU – Unemployment Exploration

[Andrew]

BYU Student Author: @Andrew
Reviewers: @Jonathan_Weston, @Jae
Estimated Time to Solve: 120 Minutes

We provide the solution to this challenge using:

• Alteryx
• Tableau

Need a program? Click here.

Overview
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.

Instructions
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

Detailed instructions:

1. Load each dataset into Alteryx, transform the data so it contains three columns (date, state, value).
2. 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.
3. 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”.
4. 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.
5. Output each dataset to a .csv file (“region_unemployment.csv” and “national_unemployment.csv”)

Section A Check Figures

Output check figures:

1. state_unemployment.csv – 3,876 records
2. region_unemployment.csv – 304 records
3. national_unemployment.csv – 76 records

Section B – Tableau
Using the three datasets created in Section A, create a dashboard that visualizes:

1. National and regional unemployment levels in time series format
2. National and regional unemployment rates in time series format
3. 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

Dataset Relationships:

State Parameter:

1. Create a parameter named ‘State Displayed Value’ with the value options: ‘Unemployment Rate (%)’ and ‘Unemployed Persons’.
2. Create a calculated field names ‘Parameter Value’ with the formula: IF [State Displayed Value] = “Unemployed Persons” THEN [Unemployed Persons] ELSE [Unemployment Rate] END.
3. 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.

Data Files

• Challenge50_Data.zip
• Challenge50_Data_Dictionary.docx

Solution

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

[Erick_Sizilio]

Great Challenge! I don’t have Alteryx so I used Tableau Prep to prepare the data for Tableau. Here is how my flow looks like. I pivoted the data, added the regions, and then joined the unemployement level and rate together with a join on State and Date.

[Mason_Lewis]

Challenge50Workflow.yxmd (89.9 KB)

I’m really proud for having figured out how to organize and combine the data from these tables! I struggled initially to convert the date from string to an actual date but I found the DateTime tool which was really cool. Using Alteryx in this way opened my mind to how this tool can be utilized in the real world. Great use of real world data coming from FRED. I’m gonna need some more training for part B of this challenge but excited to learn how you were able to model it like that.