3 Replies Latest reply on Nov 28, 2017 3:16 PM by swaroop.gantela

    Custom Territory with zip codes

    Charlie Powell

      Hello everybody,

       

      I am asking your help again.

      I have to display some basic KPIs like market share, but my problem is that I only get data at a territory level.

      This territory is created with zip code.

      For example, I have the profit for the territory "Paris North" that is 75017, 75018, 75019

      I do not have the territory at the level of each zip code but only at the level of Paris North.

      I would like to display these data in Tableau.  I can do it if I create another file with for each territory its zip codes.

      The problem with my way is that then I cannot manipulate the data like calculate market share. For example do,  sum of profit of a particular product in Paris North / Sum of profit of the category in Paris North.

       

      I know I can duplicate my data by zip code but I am not very comfortable with this because my file will explode and I think that it might exist another way ? Like a place where I can say : Paris North is 75017 + 75018 + 75019, and then be able to do all my calculations

       

      I wanted to use " Import Custom Geocoding" but then I have to define each zip code with only one point and I want the area to be fully colored.

       

      Thank you so much for taking the time to read my message.

        • 1. Re: Custom Territory with zip codes
          swaroop.gantela

          Charlie,

           

          This likely isn't there, but maybe it can be a first step.

           

          I think it will still work with your system of having the territory-zipcode crosswalk.

          It will be a matter of carefully calculating your market share, etc.

          Using Level of Detail calculations and a MIN function should reduce the duplications.

           

          I used calculations like this for [Profit per Product per Territory]:

          { FIXED [Territory],[Product]:MIN(

          { FIXED [Territory],[Product],[Zip Code]:SUM([Profit])})}

           

           

          and this for [Profit All Products]:

          { FIXED [Territory]:SUM([Profit per Product per Territory])}

           

          By changing the zipcode to a string, I was able to get a map

          that grouped the zips of a territory into one border.

          • 2. Re: Custom Territory with zip codes
            Charlie Powell

            Hello,

             

            I am not sure to understand.

            You advice to keep the zip code and territory in one file so to duplicate my data ?

            Is that correct ?

             

            Thanks.

            • 3. Re: Custom Territory with zip codes
              swaroop.gantela

              Charlie,

               

              The example datasource is Excel which had one data sheet as follows:

              Territory | Product | Profit

              TerrA | ProdX | $5

              TerrA | ProdY | $4

              TerrB | Prod X | $3

              TerrB | ProdY | $2

               

              and a second sheet of the territory to zipcode mappings:

              Territory | Zipcode

              TerrA | 73301

              TerrA | 73302

              TerrB | 74401

              TerrB | 74402

               

              After connecting to the datasource, I used a left join to connect the two sheets on [Territory]:

              253455join.png

               

               

               

              This will bring in all your profit data,

              and will create a duplicate of each line for each zipcode in that territory.

              The Level of Detail calculations previously described will attempt to

              bring back the raw data.


              Please see the workbook and datasource attached to the previous

              reply in the Forum thread of your post.