8 Replies Latest reply on Nov 6, 2018 6:58 AM by Catherine Brown

    Blending custom region "shapes" with sales

    Wissam Kahi
    Wissam Kahi Nov 5, 2018 9:19 AM

      Hello,

       

      I am a bit new to using maps in Tableau so forgive me if I'm not using the right terminology.

       

      I have a data file with sales for a client that are broken down by regions. Their definition of "region" is specific to the company, but I have a different mapping file that maps all 40k+ US zip codes to a specific region, so I was successful in creating a map of the regions in Tableau. I blended the 2 data files (sales by region and zip code to region mapping) so I can for example add the aggregated sales per region to the map.

       

      This seems to have limitations however. I cannot for example use the map as filter in a dashboard, and it's giving me issues when I try to add an LOD expression to the map that uses the blended data sources ("Cannot blend the secondary data source because one or more fields use an unsupported aggregation").

       

      I thought about joining the tables, but that would dramatically (and I think unnecessarily) increase the size of my data (40k+ zipx mapped to the region). Is there a way where I can upload the "shape" of each region and join it with the sales table? Any other recommendations?

        • 1. Re: Blending custom region "shapes" with sales
          Catherine Brown
          Catherine Brown Nov 5, 2018 9:26 AM (in response to Wissam Kahi)

          Without seeing a sample workbook or screenshot it is hard to help.  In 2018.3 the extract stores the data as separate files, which greatly decreases the size of extracts with joins.  When you say map, is it essentially a list of all of the zip codes, assigned to a particular sales region?  I think joining them, rather than blending, really is the best way to go.  Blending is both messy and can be really slow.  And as you seen, LODs are not possible here.  Since LODS are aggregated, you can't really aggregate across blended data sources (from what I understand).  I stay away from blending whenever possible, because of this.

          1 of 1 people found this helpful
          • 2. Re: Blending custom region "shapes" with sales
            Wissam Kahi
            Wissam Kahi Nov 5, 2018 9:39 AM (in response to Catherine Brown)

            Hello Caterine,

            Yes exactly, the file is a file of all the 40k+ zip codes mapping them to the 10 sales regions I have.

            Thanks a lot for your solution - I tried joining the tables and surprisingly it was much faster than I thought (I am using 2018.3), despite the fact my data is much more sizable now. It is working, but the only issue is that I have to look at all my calculations again now since the join basically repeats every row thousands of times (for every associated zip) ... Any general advice to work around this (I know I could rework all my formulas to only count the min or the max of every dimension per region instead of summing it but was wondering if there is a more efficient way)

             

            Otherwise I guess I will have to chose between this or the shortfalls of blending.

            • 3. Re: Blending custom region "shapes" with sales
              Catherine Brown
              Catherine Brown Nov 5, 2018 10:44 AM (in response to Wissam Kahi)

              The nice part about 2018.3 is that, it will recognize these two tables as having varying LOD automatically.  So, in your viz you won't have to actually use a LOD calculation, if it is already part of the granularity of these two data sources.

               

              I would definitely work though off of an extract, and not as a live connection if that is possible with your source.  In the extract window, you can specify that you want it to treat your two tables as separate tables rather than 1.  I was going to attach a workbook, but my connection to Tableau Maps is messing up (for some reason).  Probably me, and not them.

              1 of 1 people found this helpful
              • 4. Re: Blending custom region "shapes" with sales
                Catherine Brown
                Catherine Brown Nov 5, 2018 11:02 AM (in response to Wissam Kahi)

                Attached.  I also had to set this up on the region, so that it didn't subdivide by ZIP.

                 

                1 of 1 people found this helpful
                • 5. Re: Blending custom region "shapes" with sales
                  Wissam Kahi
                  Wissam Kahi Nov 5, 2018 11:26 AM (in response to Catherine Brown)

                  Catherine,

                   

                  Yes I was able to create my regions from the underlying zip codes as you have illustrated in your example, and the joining storing the data in multiple extracts works great. So performance wise I am quite impressed. Where I still have an issue though, is that now I have to revisit all my calculations, because while in the previous version I had 1 record for my most granular geographic split (in my case Region and District ..), now I have hundreds of records (1 for every zip code associated with the region - see illustration below where I now have 327 records instead of 1). This means I have to revisit all my calculations to make sure it does not aggregate I for any level of detail beyond the region and district.  I can do this with LOD calculations now but was wondering if there was a more efficient way?

                   

                   

                  • 6. Re: Blending custom region "shapes" with sales
                    Catherine Brown
                    Catherine Brown Nov 5, 2018 11:36 AM (in response to Wissam Kahi)

                    Hum, your screenshots didn't link for some reason.  I would recheck your calculations to be on the safe side, but in the attached example the default calculations already know the correct LOD with the data stored in two tables. In this case I did a sum of sales, and Tableau did the LOD for me (without me having to create any formulas). While technically the data is repeating between the two tables by the same level as your data, it already by default assumed this to be the case and aggregated appropriately. So, you may not need to use the LOD for more simple measures.  If you have some fancier calculations, you may need to rewrite them using a LOD.

                     

                    Perhaps it isn't aggregating appropriately, because the map already existed before switching to the multiple table method?  Maybe try creating the map again?

                    • 7. Re: Blending custom region "shapes" with sales
                      Wissam Kahi
                      Wissam Kahi Nov 5, 2018 6:27 PM (in response to Catherine Brown)

                      zCatherine,

                       

                      I think I can explain my issue with your example : region 00001 should have 15,100 of sales and region 00002 should have 19,469, but with the blending with the zip code file, I am getting region 0001 at 135,900 and region 00002 at 58,407 as you can see in the screenshots below. I am also attaching the same workbook with sheet 2 added.

                       

                       

                       

                      • 8. Re: Blending custom region "shapes" with sales
                        Catherine Brown
                        Catherine Brown Nov 6, 2018 6:58 AM (in response to Wissam Kahi)

                        I swear it was working properly the other day, maybe I didn't have my coffee that morning.  Yes, you'll just have to fix it with LOD calculations.  Unfortunately.