
In Progress
Posted
Add thirty-six (36) columns between the PLUG and the “series_id” in the original dataset and title them as follows: Column Entry Column Entry Column Name Column Entry 01 Key -- CountyYearAndMeasureCode 13 03 - Northeast 25 Seasonality 02 State ID 14 04 - Greater Metro 26 Series--Prefix 03 State Desc 15 05 - Central Mountains 27 State 04 State ID and Desc 16 06 - I-70 West 28 County 05 Key -- RegionCountyIDYearAndSeries 17 07 - West Central 29 Series-Suffix 06 RegionCountyIDAndYear 18 08 - Southwest 30 Series Complete 07 Region and County ID 19 09 - San Luis Valley 31 Measure Code 08 County ID Repeated 20 10 - Southeast 32 County Desc 09 County ID 21 Region 33 County and Desc 10 00 - Statewide 22 Region and Desc 34 Seasonality 11 01 - Northwest 23 Area Code 35 Series Name 12 02 - North Central 24 Area Type Code 36 Measure Code and Desc 3) Insert the following files as reference tabs into the file titled: “00 - [login to view URL] (Annual -- State and County Only)--Data [login to view URL]”. • 01 – Seasonality (CO).xlsx • 02 - Series (CO).xlsx • 03 - State and Counties (CO).xlsx • 04 - Measure Code (CO).xlsx 4) In the “State and County (CO)” tab, 1) extract the “County Code” from the “area_code”, 2) Concatenate the “County and Desc”, 3) Identify the “Region Desc” (Vlookup), 4) Concatenate “Region and Desc”, and 5) Concatenate “State ID and Desc” (the Desc is the Area Text)—all currently shown in grey. 5) Parse the Series ID using the following guide into the corresponding fields added to the model: Series ID Example: LAUCN080010000000003 (Unemployment Rate: Adams County, CO (U)) series_id--> L A U C N 0 8 0 0 1 0 0 0 0 0 0 0 0 0 3 Character Position -->01 02 030405060708091011121314151617181920 Area Code Area Type Code Seasonality Model Column Nbr--> 23 2425 26 27 28 29 31 Series--Prefix State County Series Suffix Measure Code 6) To create the data in “Series Complete” (Column 30), either use the trim command along with all but the last two characters of the series_id or CONCATENATE columns 23-29. 7) In columns 21 and 22, perform the following: for Column 21, use the use the County ID (column 28) to return the region from the “State and County” tab. Then use the County ID (column 28) to return the “Region and Desc” from the “State and County” tab for Column 22. 8) Sort ALL of the data by the following fields: 9) In columns 10 to 20, write code that correctly identifies each county in the correct region. 10) In column 09 (“County ID”), take the sum total of the columns 10-14 but force the calculated value to be a two-digit result (Hint: you are going to need to use the “RIGHT” function for this one). 11) In column 08 (County ID Repeated”), write code that will show the value above if the “County ID” is equal to “00”; otherwise, the same value as the “County ID”. 12) For columns 05 to 07, use the field name to show the proper analytics keys. 13) For columns 02 and 04, use the County ID to identify the “State ID” found in the “State and County” tab. The data for column 03 uses the same data as column 32 (“County Desc”). 14) For column 01, use the field name to identify the appropriate entries for the key. Data Presentation 1) Create a Regional Data Presentation that satisfies the following requirements: • A Pull-Down which shows all of the regions available • An entry field for the user to enter a four-digit year. • A two-digit ID code from 01-65 (Column 1). • An automatic list which shows the counties in each region (Column 2). • A column for each of the following items (Columns 3-6): 03 - unemployment rate 04 - unemployment 05 - employment 06 - labor force • A field titled “Calc UE Rate” which is equal to [(04 – Unemployment)/(06 – Labor Force)]. 2) Create a pie chart visualization of the Total Labor Force. The pie chart must have a header describing what is being displayed (top), a label on the pie chart entries displaying the county name with the value on a separate line of the label, and a legend of the colors (to the right). EXTRA CREDIT: Write a hidden layer of the data that will automatically rank the data from greatest populations to least (5% Extra) and then have the label only show the top 5 entries with the greatest 5 of the pie chart pulled out of the rest of the pie (2% Extra). TIME SERIES 3) Create a Time Series Summary that satisfies the following requirements: • A Pull-Down which shows all of the regions available • A Pull-Down which shows the valid counties available for the region selected in the previous pull-down. 4) Create a table that shows: • “# of People Employed” • “# of People Unemployed” • “Total Laborforce” • “Unemployment Rate” • Display data from 1990 to 2020 in five (5) year increments, then from 2020 to 2025, display all years (i.e. 2020, 2021, 2022, 2023, 2024, & 2025). 5) Create a “Dual-Axis” chart which the "# of People Employed" and "# of People Unemployed" on a stacked bar chart (with the "# of People Unemployed" appearing on the top of the stack), then add the corresponding unemployment rate as a secondary axis. 6) Add an appropriate title, add labels to the chart entries, label all axes (vertical and horizontal), and ensure the chart is visually appealing. EXTRA CREDIT: Add a “Fixed” overlaid statewide unemployment rate line chart to be used as a “comparison” to the selected county (3% Extra). (Be sure your charts and tables are all properly labeled and titled—PRESENTATION IS VERY IMPORTANT!!!)
Project ID: 40787281
55 proposals
Remote project
Active 4 days ago
Set your budget and timeframe
Get paid for your work
Outline your proposal
It's free to sign up and bid on jobs