Sunday, 5 February 2017

Blending MEP data to UK regions map

This part 3 of my MEP analysis: after presenting the Tableau visualisation and discussing the creation of the map, I look into using the list of all UK MEPs as a secondary datasource blended with the map.
Counting the MEPs per English region is the easier case. Region is the linking field, and we can filter on a field from the secondary source (Party) with no problem, as we are using a simple aggregation, the count (CNT). For Scotland etc. the added complication is having to link on the devolved administration which we throw into the level of detail of the relevant 'Latitude (generated)' mark.

I mentioned that the count is a simple aggregation, other similar aggregations that 'tolerate' filters from secondary sources are SUM and AVG. Things get trickier when trying to look at parties, as we have to use the distinct count. We only want to count a party once in each region it occurs in, not once for every MEP.

So how do we filter, e.g. for regional vs. UK wide parties? This is a classic case were a parameter is necessary. We right click at the bottom left, choose 'Create Parameter' and give the options we want in the menu:
Then we also create a (lower case) party calculated field driven by the parameter and we use the distinct count of this calculated field for the label and the colour:
This deals with one major issue with using secondary sources. Now let's consider another one. We've grouped our parties by regional and UK wide appeal, and we don't want to filter, but we want the tooltip to tell us what party appeal MEPs a region has as we hover over the map with the mouse. Tableau likes to aggregate anything coming from the secondary source along the linking dimensions, so it will aggregate the appeal of parties of each region, and the default aggregation for a string is ATTR(). This is perfectly fine in England where no regional parties get elected, it will return 'UK wide'. But what about the devolved administrations where regional and UK wide parties are both represented? There's a dirty hack, and as usual it involves a calculated field.

min([party appeal])+(if max([party appeal])!=min([party appeal]) then ' & '+MAX([party appeal]) else '' end)
MIN and MAX on strings is fine when there are only two options like in this case, so now instead of the hated '*' our devolved regions return 'UK wide & regional'. If we were dealing with a string field that had more than two values, MIN and MAX could still be useful to return an 'a-z' type range, if applicable. If it gets too complicated, maybe a blend is seriously limiting the analysis and another approach should be followed.

No comments:

Post a Comment