2 Replies Latest reply on Mar 7, 2018 3:25 AM by Łukasz Majewski

    Educational Brain Teaser - Custom Territories

    Łukasz Majewski

      This is about displaying custom territories (country groups).

       

      1. Data
        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

        

      CodeFAF Region
      801Canada
      802Mexico
      803Rest of Americas
      804Europe
      805Africa
      806SW & Central Asia
      807Eastern Asia
      808SE 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

       

       

           2. Objective

      Use this data to display all the regions in tableau map along with all related information like this:

      EBT Custom Territories.gif

       

       

      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

        • 1. Re: Educational Brain Teaser - Custom Territories
          Łukasz Majewski

          It turns out that this can be achieved pretty easily by data blending. I wanted to avoid this approach as blending has its limitations but in this case it works well.

          Anyway my goal was to show a feature in tableau that enables use of custom territories out of countries even though your data granularity is on the territory level and not underlying countries without a need to explode the source data.

           

          So let me add one more requirement: display distinct count of commodities in map's tool tip:

           

          1 of 1 people found this helpful
          • 2. Re: Educational Brain Teaser - Custom Territories
            Łukasz Majewski

            Well, my challenge did not gain much interest, probably I complicated the task of creating the data source too much.

            Nevertheless let me reveal my recent discovery about these custom territories.

             

            It turns out tableau only needs the countries constituting a custom territory (grouping) once - when we define it:

            Afterwards the countries are not required and may be fitered out. Now all we need is our territory names and not the countries.

            This means that we need to inject dummy records with the country code and corresponding territory - here there are 247 of them provided by the UNSD. Here is how:

             

            1. Excel

            I simply inserted 1 row to the FAF_STATE_2015.xlsx and just set fr_orig to 0 for easy joining and identification:

            such modified table is joined with UNSD territories like this:

            and then FAF table with actual foreign region is joined as follows:

            Note that [Code] field comes from UNSD table while [FR Dest] and [FR Orig] from primary data table. Here is the result:

            On top we only have our dummy records with the countries; actual data keeps the territory level of granularity.

             

            Now this is the trick completed:

            Custom Territories Trick.gif

            Note the rows count in Sheet1 - after defining our custom territories from ISO country codes - only present in the 247 dummy rows - we simply get rid of them. And the thing is tableau retains these country groups even though all the records in use now no longer contain country codes! Is it not amazing?

             

            2. SQL

            So custom SQL makes data source preparation easier and we do not modify source tables:

             

             

            I hope it may be a helpful workaround in cases where we want to display costom territory from a group of built-in geographic roles without dedicated shape files and without exploding the source data by cross joining. Also I wish tableau allowed such grouping (of known countries, states, postal codes, etc.) without such ado

             

            Cheers,

            Łukasz

            2 of 2 people found this helpful