This is about displaying custom territories (country groups).
I used Freight Analysis Framework data for this excercise. It consists of information about freight of certain goods between the states (US) and other countries grouped into regions. Those regions are the following
|803||Rest of Americas|
|806||SW & Central Asia|
|808||SE Asia & Oceania|
These regions consist of countries defined by UNSD and I have attached the relevant table (FAF_UNSD_Regions_and_Countries).
The other files/tables:
- FAF_STATE_2015 - the primary FAF data limited to the year 2015
- FAF_States - US states table joined with the primary table on code = dms_orig or dms_dest
- dictionary contains several tables you will need:
- FAF Foreign Regions - the 8 regions shown above joined on code = fr_orig or fr_dest (there is always either one)
- FAF Commodity Codes - joined on code = sctg2 field in the primary table
- FAF Modes - may be ignored
Use this data to display all the regions in tableau map along with all related information like this:
In this visualization I filtered out inter-state-only freight leaving 437519 records (all the flow between foreign regions and states). I did not care about limiting columns (although I did not use most of them probably) and my extracts are around 12MB (excel) and10MB (SQL). The reason I gave quite big dataset is so that you do not brutally cross join all the data with all the countries
3. Additional notes
I do not expect you to recreate the full dashboard. The point is to be able to use custom territories with this data.
The data source may be freely modified if required.
I did not use custom shapes as I found them rather difficult to obtain and decent quality shape files are quite heavy. But I welcome a more cunning solution with shape files should anyone propose one.
Attached are two versions of this data source: excel files and MDF file which may be attached to a SQL Server instance (you may install a free SQL Server Express). That's because SQL Server connection allows custom SQL which is a big advantage in such cases. But I managed to build the same thing using excel files with one little modification of a source table. Here I published my two versions:
So this teaser is about using maps with custom territories and tweaking your data source to serve your needs. Or maybe there is a completely unexpected (to me) approach that I did not think of?
I think it might be a good excercise and a challenge for some of you folks