top of page

Alteryx Multi-Row Meets Tableau for week 2, 2019 #MakeoverMonday

#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 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 ,

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 refer