#MakeoverMonday week 2, 2019.; Freedom of the Press
Using Alteryx’s Row Generating and Multi-Row Formula Tools to visualize global population trends in Tableau.
Topics Covered
1. Data blending with Alteryx
2. Multi-Row Formula tool use case
3. Dashboard design with LOD in mind
4. Tree-Maps and Shape Maps in Tableau
5. Highlight Actions
As an American, I was taught from a very early age that freedom of religion, of speech, to peaceably assembly, and the protection of the free press under law were the most important elements of a democratic society. Here is how we lay out protection for the free press in the Bill of Rights (the first 10 articles of the US Constitution):
“Congress shall make no law respecting an establishment of religion, or prohibiting the free exercise thereof; or abridging the freedom of speech, or of the press; or the right of the people peaceably to assemble, and to petition..."
That is great and all, but my fellow Americans and I only make up about 4% of the World’s population. Luckily FreedomHouse.org has shared “Freedom’s Dark Horizon” for the second week of the #MakeoverMonday social data project in 2019.
The questions I hope to answer in this data visualization are
1. What % of the world protects the rights of the free press and
2. Is the world trending a more, or less, free press?
To the data:
In this data set there is a nation, two dates referring to when the results were published and the year_covered which is the actual date range being reported on, a quantified level of free-press score, then a status field which buckets those scores into three groups.
Per usual I will be adding in a complimentary data source. In this case, I will attempt to look beyond borders toward the people impacted by bringing in a global population table covering the same countries and time period. General geographic groups are included as well. This is important for all viewers because many (including myself) can be very surprised by seeing a nation’s population compared to land mass and the Free press impacts people, not land.
Pulled from Wiki, the world population data looks like this in Excel:
While the data provided for the Free Press metrics is tabulated by year, the population data is only recorded every 5. Not great for Tableau, but this week I will use Alteryx to transpose and blend these two data sets.
For the sake of simplicity, I am going to assume a linear growth trend to break down the world population data into specific years. This is going to help me break down the trending analysis for those nations moving from free press towards authoritarianism, and Vic versa.
First step is to connect to the data.
As you can see, Alteryx see the first column of countries, and the yearly total / % of population fields as well. Looking closer, I don’t think the % of population column is needed as I can do that calculation very easily in Tableau; anything to reduce the possibility of data validation issues is always a useful step.
What I need to do first is transpose (columns to rows) the yearly total and rate fields so that by data looks more like the original data source which has columns so country, year, then the values. In the Transform Tools, select the Transpose tool and connect it to the workflow.
Once connected, configure the tool by selecting which fields are supposed to be “Key Fields” versus “Data.” Being a Tableau junky, I tend to say “dimensions” for Key Fields and “Measures” for the data.
This is not to say that the “data” in a transpose tool always must be whole or decimal number values, it just helps me remember that the “key fields” are the dimensions I am splitting the data on.
Here, I set only the country as a Key Field; Add in a Select Tool to change the column names, and now the resulting data looks much easier to deal with in Tableau.
After connecting another select tool to the True connector on the Filter Tool to reset the Year field back to a string, I now have my data in the desired format and filtered up to match with the #MakeoverMonday Data. While it is not 100% accurate to input a constant linear change between the 5 years gaps, it is far more accurate than having the gaps in the data or trying find actual annual world population for each country, but as it turns out...
I had absolutely no idea how to do that in Alteryx.
Luckily at Cleartelligence I work with some of the most skilled Alteryx professionals in the BI business, and one of our finest, Justin Grosz, was there to help.
So Justin, how do I do this in Alteryx?
“Happy to Help Will, and may I say how awesome it is that your readers made it this far through the blog to find out how your giant colored squares look so cool on that gorgeous tree-map and striking dark boarder map; You really inspire me to be a better data artist and person!”
OK he did not say all of that; I had yet to actually build the visualizations because I needed his expertise on the data prep, but I did tell him this was for a blog post, so I am sure Justin is cool with me taking some editorial license with his comments and the timeline, and using this hilarious run-on sentence to reward you for sticking with me this long…
Here is the solution along with Justin’s notes and the configuration of each tool.
When creating the final data set, the one issue that we had was that the data was in 5 year increments instead of per year. To get a yearly estimate, we decided to use two less commonly used tools in Alteryx, Generate Rows and Multi Row Formula, to get the data we want for Tableau.
* Generate Rows: Allows us to do a “loop” to create new rows based on a condition. This allowed us to create rows per year instead of one row per 5 years.
* Multi Row Formula: Instead of doing calculations across columns, this allows us to perform functions within the column. Using this tool allowed us to create a “counter” to multiply the growth rate per year
After using those tools, we were then able to do a simple growth calculation to get an estimated population per year. This was an advantage because it:
1. Allowed us to expand our data set
2. Gives additional interaction for the user of the dashboard to see data on a per year basis now.
Another thanks to Justin Grosz. Please make sure you check out his amazing three part blog on using Alteryx and linear regression https://www.cleartelligence.com/blog/detecting-ghosts-with-boo-leans-part-1 ,
So now we are back to the viz, and before creating the views here are the original questions I was hoping to answer;
1. What % of the world protects the rights of the free press?
2. Is the world trending a more, or less, free press?
Starting in reverse order from last week, revealing the final visualization before the how to’s.
My final data has the name of each country, a geographic region I added along with the population data, the total population and Freedom House score by year, and their bucketed status category. The first visualizations I built were the stacked bar charts and triple axis line and shape charts. OK that is not a tripe axis chart, but with transparent sheet backgrounds, there is really no difference!
The line chart is a dual axis; using the same value (% of total population for each year split by status) there is a line and square shape chart on a synchronized axis. The chart that lays over it is a simple shape chart created from a duplicate, using bigger squares and a reference line to act as a highlight and labels, and filtered down to only show one year at a time using a page control. The page control with synchronize with two other charts I plan on using and make so some pretty awesome animation functionality.
And now when I set the background color to NONE, it actually means NONE!
The two sheets layover each other in this final view.
The design is intended to create vertical level of detail calculation. The stacked bar charts with the dual axis line chart in the upper third use the status categories provided in the original data and a familiar left to right expression of time.
I wanted to use a dramatic color scheme, really did NOT like the purple/green/yellow example and was actively trying to avoid color blindness issues. As an American, when I hear “Freedom” I think red white and blue (as I am sure the French, Russians, Dutch, English, Aussies, Norwegians, North Koreans, and about twenty other nations that use that same palette for their respective flags do to). Blue, gray, Orange is always a safe color range to experiment with.
I chose to visualize the expanding global population with a tree-map on a page control. This will give you animation capabilities in Tableau Desktop. The tree-map will use the Freedom House score for each year of data, grouping the results by country and geographic regions.
To give scale to the tree plot as it expands from left to right as the timeline moves, I will add a highlight table.
The fitler on Year is what restricts the view to only show every 5 years. This makes for a cleaner final view.
The views lay together on the final dashboard with the page under the highlight table. The intention of designing these views together is to promote user interaction and provide context without having to explain the mark sizes and animation functions in text, or show a default format scale or legend.
Examples: Note the bottom right corner. The end of the tree-map lines up with the year noting the global population.
Here, the page control is set on 2000.
Here it is at 2010:
Country names and population are included in the tooltip.
Lastly is the map; the design choice of using the square shapes was an imagination of the squares in the tree-map floating down and hovering over their respective locations on the globe (if anyone knows how to do that, please find me).
By adding a slight transparency and black border to the color settings in the shape formatting,
Then increasing the shape size to make the relative population scale easily visible,
The design comes together displaying three levels of increasing detail, and answers the original questions quite effectively.
The final design function was to tie all the views together with dashboard actions; a hover action highlight on the free press status categories on all the views, then a select highlight between the tree-map and shape map by country.
Here is how the highlight action looks on the viz.
Create the action by finding actions in the dashboard tool bar menu, then selecting highlight, and OK.
Set the action type to hover, sourced by hovering over the views in the top third of the viz, but targeting all views except the population highlight table scale we created in gray tones. If one of the views does not work right on testing, go and make sure that the status is included into each viz.
The next action is a select highlight, so that a user can select a single nation or lasso a group of countries on either the tree-map or the status map, to highlight them the opposite view. Aside from choosing “select” instead of “hover”, only the sheets with “country” in the same level of detail as the views; in this case the tree-map and the shape map.
Please leave your comments and feedback below!