# County Health Profile: Tableau Rebuild Guide

This guide lists everything needed to rebuild the County Health Profile dashboard in Tableau Desktop or Tableau Public. The finished HTML dashboard in the `dashboard` folder shows the target layout. Plan on 45 to 60 minutes.

Source: County Health Rankings & Roadmaps, 2024 Annual Data Release, University of Wisconsin Population Health Institute. Original sources and data years for each indicator are listed on the Definitions sheet of the data file.

## 1. Connect to the data

1. Open Tableau and connect to **Microsoft Excel**, then choose `data/County Health Profile - Tableau Data.xlsx`.
2. Drag the **Indicators** sheet to the canvas. Add **Definitions** with a relationship on `indicator_id` if you want the definition text in tooltips (the Indicators sheet already carries unit, direction and data years, so this is optional).
3. In the data pane, confirm these field types:

| Field | Type | Notes |
|---|---|---|
| geo_id | String | Five-digit FIPS code stored as text (00000 is the United States, 45000 is South Carolina). Keep it as a string so leading zeros are not lost. |
| state_fips, county_fips | String | Split parts of the FIPS code, useful for joining SparkMap or Census downloads. |
| county_name | String, geographic role **County** | "Union County" and so on. Blank for the state and national rows. |
| state_name | String, geographic role **State/Province** | "South Carolina". |
| geo_level | String | County, State or Nation. |
| value | Number (decimal) | Blank where the source did not report a value. |
| display_format | String | pct, usd, ratio, int or dec1. Used by the label calculation. |
| direction | String | Higher is better, Lower is better, or Not rated. |

The `state_value`, `national_value`, `vs_state` and `vs_nation` columns are already in the file. The calculated fields below rebuild them inside Tableau so that the tolerance can be changed with a parameter.

## 2. Parameters

| Parameter | Data type | Allowable values | Default |
|---|---|---|---|
| p.Indicator | String | List, added from field `indicator` | Premature death (years of life lost) |
| p.County | String | List, added from field `geo_name` (remove South Carolina and United States) | Union |
| p.Tolerance | Float | Range 0 to 0.20, step 0.01 | 0.05 |

Show all three as controls. On the dashboard, p.Indicator and p.County sit in the top row and p.Tolerance can stay hidden.

## 3. Calculated fields

Create these in the order listed, because later fields use earlier ones.

```
Is Selected Indicator
[indicator] = [p.Indicator]

Is Selected County
[geo_name] = [p.County]

State Value
{ FIXED [indicator_id] : MAX(IF [geo_level] = "State" THEN [value] END) }

National Value
{ FIXED [indicator_id] : MAX(IF [geo_level] = "Nation" THEN [value] END) }

Pct Diff vs State
([value] - [State Value]) / [State Value]

Flag vs State
IF [geo_level] <> "County" OR [direction] = "Not rated" THEN "Not rated"
ELSEIF ISNULL([value]) THEN "No data"
ELSEIF ABS([Pct Diff vs State]) < [p.Tolerance] THEN "Similar"
ELSEIF ([value] > [State Value]) = ([direction] = "Higher is better") THEN "Better"
ELSE "Worse"
END

Flag vs Nation
IF [geo_level] = "Nation" OR [direction] = "Not rated" THEN "Not rated"
ELSEIF ISNULL([value]) THEN "No data"
ELSEIF ABS(([value] - [National Value]) / [National Value]) < [p.Tolerance] THEN "Similar"
ELSEIF ([value] > [National Value]) = ([direction] = "Higher is better") THEN "Better"
ELSE "Worse"
END

Value Label
CASE [display_format]
WHEN "pct"   THEN STR(ROUND([value], 1)) + "%"
WHEN "usd"   THEN "$" + STR(ROUND([value], 0))
WHEN "ratio" THEN STR(ROUND([value], 0)) + ":1"
WHEN "int"   THEN STR(ROUND([value], 0))
ELSE STR(ROUND([value], 1))
END

Sort Least Favorable First
IF [direction] = "Higher is better" THEN -[value] ELSE [value] END

Counties Worse Than State
{ FIXED [indicator_id] : SUM(IF [Flag vs State] = "Worse" THEN 1 ELSE 0 END) }

Counties Better Than State
{ FIXED [indicator_id] : SUM(IF [Flag vs State] = "Better" THEN 1 ELSE 0 END) }

KPI County Value
MAX(IF [Is Selected County] AND [Is Selected Indicator] THEN [value] END)

KPI State Value
MAX(IF [geo_level] = "State" AND [Is Selected Indicator] THEN [value] END)

KPI National Value
MAX(IF [geo_level] = "Nation" AND [Is Selected Indicator] THEN [value] END)

KPI County Flag vs State
MAX(IF [Is Selected County] AND [Is Selected Indicator] THEN [Flag vs State] END)

KPI County Flag vs Nation
MAX(IF [Is Selected County] AND [Is Selected Indicator] THEN [Flag vs Nation] END)

Highlight County
IF [Is Selected County] THEN "Selected county" ELSE "Other counties" END
```

Note: Tableau's STR() does not add thousands separators. For the KPI cards, format the number fields directly (Format > Numbers > Number (Custom) with 0 decimals and a thousands separator) instead of using Value Label.

## 4. Worksheets

| Sheet | Build | Filters | Color and labels |
|---|---|---|---|
| **Map** | Double-click `county_name`, then add `state_name` to Detail. Put `value` on Color. | geo_level = County; Is Selected Indicator = True | Sequential teal palette (light #E2F0EC to dark #0B4A4F). Put `Highlight County` on Detail and add a 2.5 pt orange border (#C9821A) through a dual-layer map or a second marks layer filtered to the selected county. |
| **Ranked Bar** | `geo_name` on Rows, `value` on Columns, bar marks. Sort `geo_name` by Sort Least Favorable First, descending. | geo_level = County; Is Selected Indicator = True | `Highlight County` on Color (#C9821A selected, #2F7F80 others). Add reference lines on the value axis for State Value and National Value, solid 1 pt dark lines labeled "SC" and "US". Value Label on Label. |
| **KPI County** | KPI County Value on Text. Add KPI County Flag vs State and KPI County Flag vs Nation as second and third lines. | none needed | Large serif number, flags in small text. Title uses `<p.County> County`. |
| **KPI State** | KPI State Value on Text. | none | Same card style. |
| **KPI Nation** | KPI National Value on Text. | none | Same card style. |
| **Range Strip** | `value` on Columns, circle marks, geo_level on Detail. | Is Selected Indicator = True | Selected county orange and larger; SC and US as dark tick marks; other counties gray. |
| **Indicator Matrix** | `indicator` on Rows (sorted by indicator_order), `geo_name` on Columns, Value Label on Text, square marks. | Domain filter set by an action from the selected indicator, or a domain quick filter | `Flag vs State` on Color: Better #DCE8F5, Worse #F6DED6, Similar and Not rated white. Selected county column outlined through a second calculated color or a border on Highlight County. |

## 5. Dashboard layout

Size the dashboard at 1600 by 1000 pixels with a light gray background (#EDF1F0) and white containers.

```
+--------------------------------------------------------------------------------+
| Title: Upstate South Carolina County Health Profile          (text, Source Serif)|
| Subtitle and source line                                                         |
+--------------------------------------------------------------------------------+
| [p.Indicator control]   [p.County control]                     Domain: <domain>  |
+------------------+--------------+--------------+------------------------------+
| KPI County       | KPI State    | KPI Nation   | Range Strip                  |
+------------------+--------------+--------------+------------------------------+
| Map (about 600 px wide)        | Ranked Bar (remaining width)                  |
|                                | Definition text (sheet with definition field) |
+--------------------------------+-----------------------------------------------+
| Indicator Matrix (full width)                                                 |
+--------------------------------------------------------------------------------+
| Footer: Source: County Health Rankings & Roadmaps, 2024 Annual Data Release.   |
+--------------------------------------------------------------------------------+
```

Dashboard actions:

1. **Change Parameter** action from Map and Ranked Bar: on select, set p.County to `geo_name`.
2. **Change Parameter** action from Indicator Matrix: on select, set p.Indicator to `indicator`.
3. Turn off "Use as filter" on all sheets so a click updates the parameters instead of filtering the other views.

## 6. Fonts and colors

| Role | Value |
|---|---|
| Headings | Source Serif 4 (or Georgia if unavailable), semibold |
| Body and labels | Public Sans (or Tableau Book) |
| Brand dark teal | #0E4F55 |
| Bar teal | #2F7F80 |
| Selected county | #C9821A |
| Better than state | #2B6CB0 text on #DCE8F5 fill |
| Worse than state | #B9472B text on #F6DED6 fill |
| Similar or not rated | #7F8B8E text on #EEF1F1 fill |

To add the map palette as a custom sequential palette, add this block inside the `<preferences>` element of `My Tableau Repository/Preferences.tps`:

```xml
<color-palette name="County Profile Teal" type="ordered-sequential">
  <color>#E2F0EC</color><color>#B4D9D0</color><color>#7FBDB2</color>
  <color>#4A9B93</color><color>#226F72</color><color>#0B4A4F</color>
</color-palette>
```

## 7. Swapping in new data

The Indicators sheet uses one row per geography and indicator. To refresh it with newer County Health Rankings, CDC PLACES or SparkMap downloads, keep the same column names, keep `geo_id` as text, and add or replace rows. New indicators also need a row on the Definitions sheet with a unit, display format and direction. Every calculated field above works on any indicator in that format, so the dashboard does not need to change.

## 8. Check before publishing

1. Union County, premature death: the KPI card should read 15,926 with "Worse" against both SC (10,318) and US (7,972).
2. Infant mortality: Abbeville and Union should show no value on the map and in the matrix.
3. Greenville County, adult smoking: 14.6%, better than SC (16.3%) and similar to the US (15.0%).