Section 1 .windows.net



Day 2 - Introduction to Data Visualization with TableauThis lesson covers:Navigating the Tableau workspace; Creating calculated fields and charts; Sets and Parameters Pt.1 Goal:Create an interactive map to view IRS Statement of Income migration (inflow/outflow) by County or EDDContents TOC \o "1-3" \h \z \u Section 1 PAGEREF _Toc55413100 \h 2Getting Started – Connecting to the Data PAGEREF _Toc55413101 \h 2Getting Started – Saving our Workbook PAGEREF _Toc55413102 \h 3Getting Started – Understanding our Data PAGEREF _Toc55413103 \h 7Creating our First Chart – Outflow by County PAGEREF _Toc55413104 \h 11Section 2 PAGEREF _Toc55413105 \h 19Creating a County Net Migration Table PAGEREF _Toc55413106 \h 19Creating a County Net Migration Map PAGEREF _Toc55413107 \h 29Section 3 PAGEREF _Toc55413108 \h 33Creating an Economic Development District Net Migration Map PAGEREF _Toc55413109 \h 33Section 4 PAGEREF _Toc55413110 \h 43Creating Total Population and Total Population Difference for Counties and EDDs PAGEREF _Toc55413111 \h 43Replacing CBSA with County Value PAGEREF _Toc55413112 \h 51Section 5 PAGEREF _Toc55413113 \h 53Creating a Drill Down Map for our EDD and County Data PAGEREF _Toc55413114 \h 53Section 1Reference the “2017-2018 County Outflow Migration data Part 1” Tableau workbook for this section.If using Tableau public, those workbooks are available to you HERE.Getting Started – Connecting to the DataConnect to Data -> Text File -> “County Outflow 17-18” (located in the Day 2 Course Materials folder)A data table should load on the “Data Source” SheetMake sure “Y1 County Name”, “Y1 State Name”, “Y1 State Abbreviation” and their corresponding “Y2…” variables load with the geographic role set (indicated by globe value“#” indicates a number type variable, “Globe Symbol” indicates geographic string variable, and “ABC” indicates a string type variableContinuous variables (measures) are green, and Categorical Variables (dimensions) are blue“County Outflow 17-18.csv” should appear in the Data Relationships PaneGetting Started – Saving our WorkbookFor Tableau Desktop UsersUnder “Connection” in the top-right corner, select “Extract”Your workbook will now refreshIn the top-left menu, select File -> Save As, and either save your workbook as a “Tableau Workbook” or a “Tableau Packaged Workbook”Packaged workbooks save the extract with the Tableau file. Its best to use this file type when sharing your data with other people so the data connection is maintained.Workbooks save the extract in a separate location. Its best to use this file type when your workbook is saved in the same file location. The data connection has to be refreshed when the file or data source (extract) is moved.Extracts allow our data to load faster, but extracts are a static query (snapshot of data). Live connections automatically refresh data in real time.If you save your file as a workbook, you’ll be prompted to save the data extract as a hyper file. Save the file in an easy-to-reference location.If you save your file as a packaged workbook, Tableau will prompt you to save the extract.Save your workbook as “2017-2018 County Outflow Migration Data” or another convenient name.Instructions for Tableau Public UsersTableau Public doesn’t allow you to create an extract of your data, and only allows you to save the view of the data. However, you can still save your work to your Tableau Public page.You’ll want to save your file to Tableau Public AFTER you’ve created some visualizations.Go to any sheet in your workbook. Make sure something is in the sheet (e.g., drag Adjusted Gross Income to the viz pane.)In the top-left corner, select File -> Save to Tableau Public As.You’ll then be prompted to sign into your Tableau Public account. Create one for free if you don’t have an account.Save your workbook as “2017-2018 County Outflow Migration Data” or another convenient name when prompted.Your visualization should now open in the browser.Select “Edit Details” under the viz to edit the file’s information as desired.Navigate back to your profile page by clicking on your picture and selecting My Profile.Here you can set the access settings for your visualization.Hide your visualization by clicking the “eye” symbol.Download your visualization by clicking the “box and down-arrow” symbol.Getting Started – Understanding our DataNavigate to “Sheet1” and double click the sheet nameTitle your sheet “Orleans Parish Outflow by County” by typing into the highlighted sheet name. Feel free to replace “Orleans Parish” with the county you want to focus on.Counties in Louisiana are called Parishes. Counties in Alaska can be called “Boroughs” or “Census Areas”.The Title at the top of the sheet will have the same name as the sheet nameDouble-click the title to customize, and see that the default is “<Sheet Name>”. Click OK.Right-click on a dimension and select “Aliases”Let’s understand our data better! I cleaned and joined data for our lesson already.Each row of data contains 2 pieces of geographic information: The county in which a population group was located in 2017 (Y1 – Year1) and the county in which the population group was located 2018 (Y2 -Year 2). Y1 data is entirely at the county level, while some Y2 data is at larger geographic levels due to suppression issues. We can also check out the data dictionary for more information.Dimensions (Categorical Variables):Y1 Census CBSACBSA in which population group was located in Year 1. Matched to Y1 County Name.Y1 County NameCounty in which population group was located in Year 1.Y1 Economic Development DistrictEconomic Development District in which population group was located in Year 1. Matched to Y1 County Name.Y1 Metropolitan/Micropolitan Statistical AreaDelineates whether a Year 1 population group is located in a CBSA-Metropolitan Statistical Area, CBSA-Micropolitan Statistical Area, or is located in a Non-CBSA area.Y1 State AbbreviationState name in which county is located postal code abbreviation in Year 1.Y1 State NameState name in which county is located in Year 1. Y2 Census CBSACBSA in which population group was located in Year 2. Matched to Y2 County Name. Is replaced with state or region name in cases of suppression and foreign area in cases of out-of-country migration.Y2 County NameCounty in which population group was located in Year 2. Is replaced with state or region name in cases of suppression and foreign area in cases of out-of-country migration.Y2 Economic Development DistrictEconomic Development District in which population group was located in Year 2. Matched to Y2 County Name. Is replaced with state or region name in cases of suppression and foreign area in cases of out-of-country migration.Y2 Geography TypeDelineates whether a Year 2 population is located in a CBSA Metro/Micro/Non-CBSA area in the case of county data. Foreign migration is listed as either “Expatriates”, “Overseas Military Installations”, “Puerto Rico”, or “Virgin Islands”. County-level suppressed data is listed as same “State” or different state “Region”.Y2 State AbbreviationState name in which county is located postal code abbreviation in Year 2.Y2 State NameState name in which county is located in Year 2.Measures (Continuous Variables):Adjusted Gross Income (AGI) (thousands)Value in $$$ of row income, adjusted for tax considerationsNumber of exemptionsNumber of tax exemptions claimed by a row.Number of returnsNumber of tax returns filed by a row.Y1 CountyfipsY1 County FIPS number. Could also be classified as a dimension.Y1 State + County FIPSConcatenation of Y1 County and Y1 state FIPS numbers to form a unique identifier. Could also be classified as a dimension.Y1 StatefipsY1 State FIPS number. Could also be classified as a dimension.Y2 CountyfipsY2 County FIPS number. Could also be classified as a dimension.Y2 State + County FIPSConcatenation of Y2 County and Y2 state FIPS numbers to form a unique identifier. Could also be classified as a dimension.Y2 StatefipsY2 State FIPS number. Could also be classified as a dimension.Database Design PrinciplesEach entity (topic) should have its own table or column.Each row (record) and column (variable) should be unique.The sequence of rows (records) and columns (variables) is not important.Each cell should be atomic:One value per cell.No repeating groups of data.Tall vs wide data – most functions are enabled for tall data.Creating our First Chart – Outflow by CountyThe goal of this chart is to identify what counties people from Orleans Parish are moving to.Drag the field “Y1 County Name” to the Filters card. This restricts our analysis ONLY to population groups that were in Orleans Parish in Year 1.Under General-> click Select from List -> click None -> Type “Orleans Parish” into the yellow Enter Search Text bar -> click the checkbox -> click Apply -> click Ok.Drag “Y2 County Name” to the Rows mark (add all members), and “Number of Returns” to the Text mark.Right-click “Y2 County Name” in the Rows mark -> Sort -> Sort by, select Field -> Set Field Name to “Number of Returns”. Change Sort by Ascending to Descending. Click the X to close the Sort window.Sort by “Ascending” orders the counties by the least “Number of Returns” to the most. Sort by “Descending” orders the counties by the most “Number of Returns” to the least.“Orleans Parish” is now at the top. This is because a majority of persons in the county did not move counties between Years 1 and 2. Let’s create a calculated field to demonstrate this.Select the drop-down menu next to the search bar in the data pane, or right-click empty space in the data pane. Select Create calculated field.Title the calculated field “Migration”. Enter the following text (quotation marks may not copy/paste correctly):IF [Y2 County Name]=[Y1 County Name] THEN "Non-Migrant "+[Y1 County Name]+”, “+ [Y2 State Abbreviation]+” Population”ELSEIF[Y2 Geography Type]="State"THEN [Y1 County Name]+" to Out-of-State "+[Y2 State Name]+” counties”ELSEIF[Y2 Geography Type]="Region"THEN [Y1 County Name]+" to "+[Y2 State Name]+" Region counties"ELSE[Y1 County Name]+" to "+[Y2 County Name] +", "+[Y2 State Abbreviation]ENDDrag “Migration” to the Rows mark and remove “Y2 County Name” by either dragging it out of the Rows mark or right clicking the field and selecting Remove.We created “smart text” that changes depending on the type of geography. We could utilize this feature to change entire paragraphs of text that are displayed.We might want to add a note to our data that the different geographies are due to suppressed data. Let’s create a filter to remove all “Non-Migrant” data from the view.Create a calculated field by selecting the drop-down menu next to the search bar in the data pane, or right-click empty space in the data pane.Title your field “Non-Migrant Population Filter”. Enter the following text:IF [Y1 County Name]=[Y2 County Name]THEN "Non-Migrant"ELSE "Migrant"ENDDrag “Non-Migrant Population Filter” to the Filters card -> Select “Migrant” -> Apply -> Ok.Now that we have a basic understanding of our data, let’s filter down our data to the Top 10 counties/geographies by population outflow.Right-click “Y2 County Name” in the Data Pane -> Create -> Set.We’re going to filter by “Y2 County Name” because it feeds into “Migration”.We can’t create a set for “Migration” because it’s a concatenation of “Y1 County Name” and “Y2 County Name”Title the set “Top 10 Counties by Outflow of Orleans Parish Taxpayers”In the set menu select Top -> By Field -> “Top” “10” by “Number of Returns” “sum” -> Ok.Drag “Top 10 Counties by Outflow of Orleans Parish Taxpayers” to the Filters card.Notice – our data didn’t filter down to the actual top 10 counties (a plurality of Orleanians moved to Jefferson Parish, the New Orleans suburbs and where I grew up!)Instead, our data filtered down to the Top 10 “Y2 County Name” (Y2 Counties) that both received the most persons from “Y1 County Name” (Y1 Counties) and had people from New Orleans move to them.We can see this if we remove the “Y1 County Name: Orleans Parish” filter. There are ties between Y2 Counties, so there is a total of 12 rows.Why did Tableau do this? It has to do with the order in which Tableau performs calculations.Tableau has its own version of PEMDAS.Tableau calculates Sets before it calculates Dimension Filters, meaning Tableau calculates our “Top 10…” Set before filtering our data to Y2 County data that originates in Orleans Parish.The Set is also calculated before our “Non-Migrant Population Filter”To remedy this, we can add our two filters to the Context, meaning Tableau performs their calculations before anything else in the sheet (but after calculations by the data source)In the Filters card, right-click the filter “Y1 County Name: Orleans Parish” -> Add to Context.Notice only 9 rows are displayed – this is because the Set filter is still using data from Orleans Parish Non-Migrants.In the Filters card, right-click the filter “Non-Migrant Population Filter: Migrant -> Add to Context.The filter should change colors from royal blue to grey. Now 10 rows should display.Quite a few non-counties are displaying (see earlier note on suppression). We could add an additional filter so only County to County flows are visualized using the “Y2 Geography Type” variable that filters out the “Region” and “State” values.In the top-right, select the Show Me pane and select the Horizontal Bars option.487680127622500If we hover over each option in the Show Me pane, Tableau will actually recommend charts based on the selection of Dimensions and Measures.Now that we’ve created a basic chart in Tableau, let’s move on to our next exercise.Here we’ll learn how to use Sets, Parameters, and Calculated Fields to make a dynamic version of this chart that we can map.Section 2Reference the “2017-2018 County Outflow Migration data Part 2” Tableau workbook for this section.If using Tableau public, that file is available to HERE.Creating a County Net Migration TableCreate a new sheet and title it “Net Migration - County”We are going to first create a net migration table that dynamically corresponds to a parameter for a county.First, we’ll create a parameter to select between counties.In the last exercise, we used “Y1 County Name”=”Orleans Parish” to filter our “Y2 County Name” data. Now we’ll use a calculated field that references our “County” parameter to select “Y2 County Name” counties.STOP! We cannot just make a “County” parameter. There are multiple counties with the same name, so we need to concatenate “County” and a state variable so that Tableau references the appropriate geography. I have tested it and won’t make you go through the steps. Have success from my failure!Create a new calculated field entitled “Y1 County State”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the field “Y1 County State” -> Enter the following formula -> Apply -> Ok.[Y1 County Name]+", "+[Y1 State Abbreviation]Create a new calculated field entitled “Y2 County State”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the field “Y2 County State” -> Enter the following formula -> Apply -> Ok.[Y2 County Name]+", "+[Y2 State Abbreviation]Right-click “Y1 County State” -> Create -> Parameter -> Title the parameter “County State Parameter” -> Ok.The values from”Y1 County State” should populate the fields.245110027241500 Now we can create a new calculated field Create a new calculated field entitled “County State”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the field “County State” -> Enter the following formula -> Apply -> Ok.In formulas: Parameters are purple text. Fields are orange text.We identify parameters by the “ABC” (vs the “=ABC” for dimensions) symbol.This set connects our “County State” parameter to our Dimensions.IF [Y2 County State] = [County State Parameter] THEN [Y1 County State]ELSEIF [Y1 County State] = [County State Parameter] THEN [Y2 County State]ELSE NULLENDDrag “County State” to the Rows Mark.Right-click “County State Parameter -> Show Parameter.Drag “County State” to the Filters card -> Select None -> Check Exclude -> Check Null -> Apply -> Ok.NOTE: My “County State Parameter” is set to “Orleans Parish”.We’ve filtered out Null values that don’t correspond to our selected “County State”. We can also filter out cases when “Y1 County State” = “Y2 County State”Right-click “Non-Migrant Population Filter” -> Duplicate.Right-click the newly created “Non-Migrant Population Filter (copy)” -> click Edit ->Replace the title with “Non-Migrant Population Filter County State” -> and enter the following formula -> Apply -> Ok.IF [Y1 County State] = [Y2 County State]THEN "Non-Migrant"ELSE "Migrant"ENDDrag “Non-Migrant Population Filter County State” to the Filters card -> Select “Migrant” -> Apply -> Ok. Right-click “County State” in the Filters card -> Add to Context.Right-click “Non-Migrant Population Filter County State” in the Filters card -> Add to Context.Because “County State” joins Year 1 and Year 2 data for each county, Tableau sums any inflow and outflow returns data if we only use the “Number of Returns” field.Instead, we need to create separate calculated fields for inflow and outflow.Now we’re going to create our “Inflow Migration” and “Outflow Migration” calculated fields for our selected “County State” by using an if…then function to separate “Number of Returns” data.Create a new calculated field entitled “Inflow Migration”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the field “Inflow Migration” -> Enter the following formula -> Apply -> Ok.IF [Y2 County State] = [County State Parameter] THEN [Number of returns] ELSE NULL ENDCreate a new calculated field entitled “Outflow Migration”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.We could also duplicate and edit the “Inflow Migration” calculated field.Title the field “Outflow Migration” -> Enter the following formula -> Apply -> Ok.IF [Y1 County State] = [County State Parameter] THEN [Number of returns] ELSE NULL ENDIn order to calculate “Net Migration” (Inflow-Outflow), we need to perform a two-step calculation.The first step will calculate the net difference between “In-Migration” and “Out-Migration”The second step will display “In-Migration” or the negative of “Out-Migration” in the case that either value is null, in which case “Net Migration” will populate a null.A double null value indicates suppressed data.We can use an if…then function to accomplish this.Create a new calculated field entitled “Net Calculation”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.This is the first part of our two-step calculation.Title the calculated field “Net Calculation” -> Enter in the following calculation -> Apply -> Ok.SUM(IF [Y2 County State] = [County State Parameter] THEN [Number of returns] ELSE NULL END)-SUM(IF [Y1 County State] = [County State Parameter] THEN [Number of returns] ELSE NULL END)We can see that “Net Calculation” is Null in the case that either “Inflow..” or “Outflow…” are NULL.Create a new calculated field entitled “Net Migration”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.This is the second part of our two-step calculation.Title the calculated field “Net Migration” -> Enter in the following calculation -> Apply -> Ok.IF ISNULL([Net Calculation]) AND ISNULL(SUM([Inflow Migration])) THEN -1*SUM([Outflow Migration])ELSEIF ISNULL([Net Calculation])THEN SUM([Inflow Migration])ELSE [Net Calculation] ENDWe can see that “Net Migration” is NOT Null in the case where either “Inflow…” or “Outflow…” are Null.We could’ve set “Outflow Migration” as negative in the “Outflow Migration” function. In this case we wouldn’t need to multiply it by -1 in the “Net Migration” function, and we’d use addition, rather than subtraction, in the “Net Calculation”.Drag “Net Migration” to the Text mark.If you had placed either “Inflow Migration”, “Outflow Migration” or “Net Calculation” in the Text mark, remove them either by dragging them out of the Marks pane; Or Right-clicking the calculated field -> Remove.Right-click the “County State” Field Label (header) -> Hide Field Labels for Rows.Now we can format our table as desired. Right-click the data table -> Format to change the formatting for your table.Different tabs (in this case Header & Pane) allow you to edit different parts of the data visualization.Selecting the dropdown next to Fields allows you to format specific fields.The formatting toolbar in the left has further options.The “A” formats text design (e.g. coloring, size).“[Paragraph Symbol]” formats the text paragraph style (e.g. line spacing, text alignment).“[Paint Bucket]” formats the coloring of the data.“[four-square]” formats the borders of the data view.“[Vertically-parallel bars]” formats lines between data rows, columns, and headers.We want to create a data table specifically for our map, so let’s use a Set to filter our rows by the total number of returns, i.e. the size of the flow between two counties.Right-click “County State” -> Create -> Set.Title the Set “Top 10 County State Returns” -> Select the Top tab -> By field -> enter the following formula -> Top 10 by “Number of Returns” sum -> Ok. Drag “Top 10 County State Returns” to the Filters card.Right-click “County State” in the Rows mark -> Sort. In the Sort window -> set Sort By to “Field” -> “Number of Returns” -> Sort Order “Descending”.Creating a County Net Migration MapNow that we have our Net Migration Chart, we can create a map that displays net migration.Create a new sheet -> title the sheet “Migration Map – County”Right-click “County State Parameter” -> Show Parameter.Ctrl-select “Y1 County Name”, “Y1 State Name”, and “Net Migration” -> Select the Show Me pane -> Maps.“Net Migration” automatically applies to Colors mark, and “Y1 County Name” and “Y1 State Name” automatically apply to the Details mark.Right-click “County State” -> Create -> Set -> title the set “County State Set” -> select the Condition tab -> By Formula -> and enter the following formula -> Ok.[County State]=[County State Parameter]Drag “County State Set” to the Filters card. The data should now filter down to the selected concatenation of county and the state abbreviation.We can see the tooltip when we hover over the selected county.In the Marks card, select Tooltip -> Delete all the text in the Edit Tooltip bar that appears.Center the text using the paragraph tool at the top of the bar.Select Insert -> “<Parameters.County State Parameter>”.Add two blank lines to the Tooltip using the enter key -> Insert -> Sheet -> Net Migration – County.In the Edit Tooltip bar, select the text inserting the sheet, select [filter="<All Fields>"] and delete it.<Sheet name="Net Migration - County " maxwidth="300" maxheight="300" filter="<All Fields>">After you delete the text, leave your cursor in the empty space -> Insert -> “<Parameters.County State Parameter>” and a new filter will populate the sheer query. Add a title to the table in the text editor, “Net Migration, Top 10 Counties by Total Migration” -> Select Ok.Our County Map with a Net Migration tooltip is now complete!Section 3Reference the “2017-2018 County Outflow Migration data Part 3” Tableau workbook for this section.If using Tableau public, that file will be made available to you for the training at HERE.Creating an Economic Development District Net Migration MapWe will need to create a new “Net Migration” chart, as well as a new “Migration Map” for EDD data.We also need to create new “Inflow…”, “Outflow…”, and “Net…” statistics that use EDD data.We can continue to display our data at the county level, but to do this, we need to create a calculated field that displays the county name instead of the EDD name.Create a new sheet and title it “Net Migration – EDD”First, let’s create our Economic Development District parameter.Right-click “Y1 Economic Development District” -> Create -> Parameter -> Change the title to “Economic Development District”.The values from “Y1 Economic Development District” should appear in the List of Values. We can change the name of any value in the parameter by double-clicking the value in the Display As column.Now we can create a dimension using calculated fields that connect the “Economic Development District” parameter the “Y1 Economic Development District” field, and the “Y1 County Name” field.Create a new calculated field entitled “EDD to County State”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the field “EDD to County” -> Enter the following formula -> Apply -> Ok.IF [Y2 Economic Development District] = [Economic Development District] THEN [Y1 County State]ELSEIF [Y1 Economic Development District] = [Economic Development District] THEN [Y2 County State]ELSE NULLENDDrag “EDD to County State” to the Rows mark. Right-click the “Economic Development District” parameter -> Show Parameter.I set my parameter to the “Regional Planning Commission”.Drag “EDD to County State” to the Filters card. Select None -> Check Exclude -> Check Null -> Apply -> Ok.Drag the “Non-Migrant Population Filter County State” to the Filters card and select “Migrant”.Duplicate “Non-Migrant Population Filter County State” -> Right-click “Non-Migrant Population Filter County State (copy)” -> Edit -> Rename the title “Non-Migrant Population Filter EDD” -> and enter the following formula:IF [Y1 Economic Development District] = [Y2 Economic Development District]THEN "Non-Migrant"ELSE "Migrant"ENDDrag the newly created calculated field “Non-Migrant Population Filter EDD” to the Filters card.Select “Migrant” -> Apply -> Ok.Right-click “EDD to County State” -> Add to Context.Right-click “Non-Migrant Population Filter EDD” -> Add to Context.Right-click “Non-Migrant Population Filter County State” -> Add to Context.Now we’ll alter the “Inflow…”, “Outflow…” and “Net…” calculations to work with our “Economic Development District” parameter.Right-click the “Inflow Migration” field -> Duplicate -> Select “Inflow Migration (copy)” (the duplicated field)Edit -> Rename the title “Inflow Migration EDD” -> and change the formula to the following:IF [Y2 Economic Development District] = [Economic Development District] THEN [Number of returns] ELSE NULL ENDRight-click the “Outflow Migration” field -> Duplicate -> Select “Outflow Migration (copy)” (the duplicated field)Edit -> Rename the title “Outflow Migration EDD” -> and change the formula to the following:IF [Y1 Economic Development District] = [Economic Development District]THEN [Number of returns] ELSE NULL ENDRight-click the “Net Calculation” field -> Duplicate -> Select “Net Calculation (copy)” (the duplicated field)Edit -> Rename the title “Net Calculation EDD” -> and change the formula to the following:SUM(IF [EDD to County State] = [Y1 County State] THEN [Number of returns] END) -SUM(IF [EDD to County State] = [Y2 County State] THEN [Number of returns] END)Right-click the “Net Migration” field -> Duplicate -> Select “Net Migration (copy)” (the duplicated field)Edit -> Rename the title “Net Migration EDD” -> and change the formula to the following:IF ISNULL([Net Calculation EDD]) AND ISNULL(SUM([Inflow Migration EDD])) THEN -1*SUM([Outflow Migration EDD])ELSEIF ISNULL([Net Calculation EDD])THEN SUM([Inflow Migration EDD])ELSE [Net Calculation EDD] ENDDrag “Net Migration EDD” to the Text mark.Let’s filter down our data to the Top 10 by “Number of Returns”.Right-click “EDD to County State” -> Create -> Set. Rename the Set “Top 10 EDD Returns” -> Select the Top tab -> By Field -> Top 10 by “Number of Returns” sum -> Ok.Drag “Top 10 EDD Returns” to the Filters card.Right-click “EDD to County State” in the Rows mark -> Sort In the Sort window -> Sort By “Field” -> Sort Order “Descending” -> Field Name “Number of Returns”.Right-click “EDD to County State” field label -> Hide Field Labels for Rows.Change the view from “Standard” to “Entire View”.Create a new sheet -> name the sheet “Migration Map – EDD”.Ctrl-select “Y1 County Name”, “Y1 State Name”, and “Net Migration EDD” -> Select the Show Me pane -> Maps.“Net Migration EDD” automatically applies to Colors mark, and “Y1 County Name” and “Y1 State Name” automatically apply to the Details mark.Right-click “Y1 Economic Development District” -> Create -> Set -> title the set “EDD Set” -> select the Condition tab -> By Formula -> and enter the following formula -> Apply -> Ok.[Y1 Economic Development District] = [Economic Development District]Drag “EDD Set” to the Filters card. The map should filter in on the selected “Economic Development District”.Unfortunately, we can only display data for the entire EDD – we can’t filter down the proportion for each county, at least with the current way the data is constructed. We might be able to use level of detail expressions to create this view.Remove “Net Migration” from the Colors mark. A map with dots appears.Select the Show Me pane -> Maps -> a blue map of the selected EDD appears. Select Color -> Set opacity to 100% -> Border to either black or white.In the Marks card, select Tooltip -> Delete all the text in the Edit Tooltip bar that appears.Center the text alignment -> Insert “Y1 County Name”, “Y1 State Name”, and “<Parameters.Economic Development District>” -> Insert -> Sheets -> “Net Migration EDD”Format the Tooltip according to the below format:<Y1 County Name>, <Y1 State Name><Parameters.Economic Development District><Sheet name="Net Migration - EDD" maxwidth="300" maxheight="300" filter="<All Fields>">Delete the highlighted text: filter=”<All Fields>”-> Insert -> “<Parameters.Economic Development District>” and replace the “All Fields” filter with the inserted parameter -> Add in the below title (“Net Migration to/from EDD, by Counties with the Top 10 Total Returns”) or a similar title -> Ok.We now have a map that displays net migration for the EDD.We’ll eventually add a drill down parameter option that enables navigation between the County and EDD map by clicking onSection 4Reference the “2017-2018 County Outflow Migration data Part 4” Tableau workbook for this section.If using Tableau public, that file will be made available to you HERE.Creating Total Population and Total Population Difference for Counties and EDDsFor CountiesOur tooltip shows net migration for the Top 10 counties by the number of returns for our selected County and EDD, but our audience might also want to know the total population in Y2, and the population change between Y1 and Y2.Create a new sheet, and name it “Total Population County”.First, we’ll create a new calculated field that matches “Y1 County State” and “Y2 County State” to the parameter.We already have the “County State” field to do this, but we need to restructure our if…then function to get the desired effect.Create a new calculated field entitled “County State Alternate”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.This is the second part of our two-step calculation.Title the calculated field “County State Alternate” -> Enter in the following calculation -> Apply -> Ok.IF [Y1 County State] = [County State Parameter] THEN [Y1 County State]ELSEIF [Y2 County State] = [County State Parameter] THEN [Y2 County State]ELSE NULL ENDDrag “County State Alternate” to the Rows mark. Then drag “County State Alternate” to the Filters card.Select None -> Check exclude -> Select “Null” -> Apply -> Ok.We need to use the fixed level of detail expression to calculate total population. This allows our calculation to query data in a manner that bypasses the data view and any filters.Create a new calculated field entitled “Population Y2 County”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the calculated field “Population Y2 County” -> Enter in the following calculation -> Apply -> Ok.{ FIXED [Y2 County State] :SUM(IF [Y2 County State] = [County State Parameter] THEN [Number of returns] END ) }Right-click “Population Y2 County” -> Duplicate -> Right-click the duplicate “Population Y2 County (copy)” -> Edit -> Rename the field “Population Y1 County” -> Enter the following formula -> Apply -> Ok.{ FIXED [Y1 County State] :SUM(IF [Y1 County State] = [County State Parameter] THEN [Number of returns] END ) }Now we have totals for Y1 and Y2 for our selected County. We can calculate the difference.Create a new calculated field entitled “Population Change Y1 to Y2”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the calculated field “Population Change Y1 to Y2” Enter in the following calculation -> Apply -> Ok.SUM([Population Y2 County]) - SUM([Population Y1 County])Drag “Population Y2 County” to the Rows mark, and drag “Population Change Y1 to Y2” to the right of “Y2 County” in the Rows mark. Format as desired.Instructions for EDDsCreate a new sheet and title it “Total Population EDD”Create a new calculated field entitled “Economic Development District Alternate”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the calculated field “Economic Development District Alternate” -> Enter in the following calculation -> Apply -> Ok.IF [Y2 Economic Development District] = [Economic Development District] THEN [Y2 Economic Development District]ELSEIF [Y1 Economic Development District] = [Economic Development District] THEN [Y1 Economic Development District] ELSE NULL ENDDrag “Economic Development District Alternate” to the Rows and the Filters cards -> In the Filter editor, select None -> check Exclude -> check “Null” -> Apply -> Ok.Create a new calculated field entitled “Population Y2 EDD”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the calculated field “Population Y2 EDD” -> Enter in the following calculation -> Apply -> Ok.{ FIXED [Y2 Economic Development District]:SUM(IF [Y2 Economic Development District] = [Economic Development District] THEN [Number of returns] END ) }Right-click “Population Y2 EDD” -> Duplicate -> Right-click the duplicate “Population Y2 EDD (copy)” -> Edit -> Rename the field “Population Y1 EDD” -> Enter the following formula -> Apply -> Ok.{ FIXED [Y1 Economic Development District]:SUM(IF [Y1 Economic Development District] = [Economic Development District] THEN [Number of returns] END ) }Now we have totals for Y1 and Y2 for our selected County. We can calculate the difference.Create a new calculated field entitled “EDD Population Change Y1 to Y2”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the calculated field “EDD Population Change Y1 to Y2” Enter in the following calculation -> Apply -> Ok.SUM([Population Y2 EDD]) - SUM([Population Y1 EDD])Drag “Population Y2 EDD” to the Rows mark. Then drag “EDD Population Change Y1 to Y2” to the right of “Y2 EDD” in the Rows mark. Format as desired.In “Migration Map – EDD”Navigate to “Migration Map – EDD” -> Tooltip -> Insert -> Sheets -> “Total Population EDD”.Format the tooltips according to the following:<Y1 County Name>, <Y1 State Name><Parameters.Economic Development District><Sheet name="Total Population EDD" maxwidth="400" maxheight="400" filter="<All Fields>">Net Migration to/from EDD, by Top 10 Counties by Total Migration<Sheet name="Net Migration - EDD" maxwidth="400" maxheight="400" filter="<Parameters.Economic Development District>">We enlarged the max width and height to 400, creating a bigger tooltip and allowing for a larger visualization in our tooltip.Repeat this process for the “Migration Map – County” using “Total Population County” sheet.Replacing CBSA with County ValueCreate a new sheet and entitle it “Net Migration - County by CBSA”Drag the “Non-Migrant Population Filter County State” dimension and drag it to the Filters card -> Select “Migrants” only -> Add to Context.Create a new calculated field entitled “County State to CBSA”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the calculated field “County State to CBSA” Enter in the following calculation -> Apply -> Ok.IF [Y2 County State] = [County State Parameter] THEN [Y1 Census CBSA]ELSEIF [Y1 County State] = [County State Parameter] THEN [Y2 Census CBSA]ELSE NULL ENDDrag “County State to CBSA” to the Filters card -> Select None -> check Exclude -> select “Null” -> Apply -> Ok -> Right-click “County State to CBSA” in the Filters card -> Add to Context.Drag “Net Migration” to the text mark.Right-click “County State to CBSA” -> Create -> Set -> rename the set “Top 10 CBSA Returns” -> Select the Top tab -> By Field -> Top 10 by “Number of Returns” sum -> Ok.Drag “Top 10 CBSA Returns” to the Filters card.Drag to rows and Right-click “County State to CBSA” -> Sort -> Sort by “Field” -> Sort Order “Descending” -> Field Name “Number of Returns” -> Exit.We could use this table to replace the table in “Migration Map – County” or create a version using EDDs (just replace designated “County State” variables with “Economic Development District” values ) for the “Migration Map – EDD”.Section 5Reference the “2017-2018 County Outflow Migration data Part 5” Tableau workbook for this section.If using Tableau public, that file will be made available to you for the training at HERE.Creating a Drill Down Map for our EDD and County Data Create a dashboard and title it “EDD to County Drilldown”.Under the Objects menu, select a “Horizontal” -> Tiled -> Drag it to the “Drop Sheets Here” area.Drag “Migration Map – EDD” inside the horizontal you just placed.The box should illuminate a blue outline to let you know its being placed in the horizontal.The dropped sheet should fill up the whole sheet.Drag “Migration Map – County” to the right of “Migration Map – EDD” (still in inside the horizontal we earlier placed)The box should illuminate blue and should place a skinny grey lineRight-click the title of “Migration Map – County” -> Hide Title. Do the same for “Migration Map – EDD”. Right-click the title of “Migration Map – EDD” -> Hide TitleSelect the “Migration Map – EDD” -> Dropdown Menu (down arrow) for More Options -> Parameters -> “Economic Development District”.Select the “County State Parameter” -> Dropdown Menu (down arrow) for More Options -> Remove from Dashboard.Also remove the “Net Migration” legend from the dashboard by selecting it -> Remove from Dashboard.We need to add in a filter and parameter value to minimize each of the maps when the other is selected.Navigate to the “Migration Map – EDD” sheet.Right-click “County State Parameter” in the Parameters card at the bottom of the Data Pane -> Edit.Scroll to the bottom of the parameter -> Where the parameter says “Add” followed by a blank cell, enter “All”.Right-click “Economic Development District” parameter in the Parameters card at the bottom of the Data Pane -> Edit.Scroll to the bottom of the parameter -> Where the parameter says “Add” followed by a blank cell, enter “All”.In the “Migration Map – EDD” sheet, drag “Y1 County State” to the Text Labels mark.Navigate to the sheet “Migration Map – County” -> drag “Y1 County State” and “Y1 Economic Development District” to the Text Labels mark.Click the Text Labels box -> Click the “…” next to Text -> Delete “<Y1 Economic Development District> from the mark label ->Apply -> Ok.This associates the data with “Y1 Economic Development District” without displaying its data.Navigate back to the sheet “EDD to County Drilldown”.In the menu, select Dashboard -> Actions Add Action -> Change Parameter.Title the action “EDD to County Action” -> Select Source Sheets “EDD to County Drilldown” and check “Migration Map – EDD” (uncheck “Migration Map – County” if checked).Set target parameter to “County State Parameter” -> Field to “Y1 County State” -> Aggregation to “none” -> Clearing the selection will, select Set Value to -> Type “All” -> Ok.This will set the “County State Parameter” to the selected sheet on the “Migration Map – EDD” and when the selection is cleared, “County State Parameter” = “All”.Don’t close the Actions window yet! Select Add Action -> Change Parameter.Title the action “County to EDD Action” -> Select Source Sheets “EDD to County Drilldown” and check “Migration Map – County” (uncheck “Migration Map – EDD” if checked).Set target parameter to “County State Parameter” -> Field to “Y1 County State” -> Aggregation to “none” -> Clearing the selection will, select Set Value to -> Type “All” -> Ok.This will set the “County State Parameter” to the selected sheet on the “Migration Map – County” and when the selection is cleared, “County State Parameter” = “All”.Click Ok in the Actions window.Now when we click on a county in our “Migration Map – EDD”, the “Migration Map – County” for the selected county appears. When we click on the selected county in the “Migration Map – County”, “Migration Map – County” will disappear. Note you may need to double click - once to select the sheet and the second to active the action.We need to add one more feature so that our “Migration Map – EDD” disappears when “Migration Map – County” displays.Navigate to the sheet “Migration Map – EDD”.Create a new calculated field entitled “When County State Parameter”Select the dropdown to the left of the Data Pane search bar or right-click the Data Pane -> Create Calculated Field.Title the calculated field “When County State Parameter” Enter in the following calculation -> Apply -> Ok.IF [County State Parameter] = "All" THEN "All" ENDDrag “When County State Parameter” to the Filters card -> Check “All” -> Apply -> Ok.Navigate back to the dashboard “EDD to County Drilldown”. The “Migration Map – EDD” should disappear when the county is selected and reappear when the county on the “Migration Map – County” is selected. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download