14 Replies Latest reply on Mar 25, 2015 8:14 AM by Florian Zehl

    Calculating sum of requests within a zip code radius

    Florian Zehl

      Hi,

       

      I'm looking for a solution for the following issue. I'm trying to sum requests for each zip code within a zip code radius. For now I'm able to enter a zip code and get all the zip codes within the zip code radius of e.g. 50 kilometers. This works well as I already get the total of requests for each zip code. But I'm not aware of it how to sum the requests for each zip code within the radius and getting just one line with the entered zip code and the total of requests. Also I just want to type in severals zip codes, getting a single line for each entered zip code and again the total of requests.

       

      Example:

      I enter the following zip codes --> 38100, 20148, 80469

       

      Result:

      zip code     requests

      38100          50  

      20148          32    

      80469          48

       

      Is there someone who have an idea for that issue? You can find attached my workbook with an example data set and the current status.

       

      Thanks in advance

      Florian

        • 1. Re: Calculating sum of requests within a zip code radius
          Matt Lutton

          What version of Tableau Desktop are you using?  This is another helpful piece of information to know, as many of us have several versions installed and like to keep things consistent when we respond -- thanks for posting a workbook, and providing details on your question!

           

          Also, to be completely sure we understand you, it might help to include a screenshot/mockup of what you hope to achieve as a final result.  I believe I follow what you're after, but a visual mockup is always a good thing!

           

          Cheers

          • 2. Re: Calculating sum of requests within a zip code radius
            Noah Salvaterra

            This post might be helpful:

            Richard Leeke's Super-Charged ZIP Code Radius-Finder

            In general I'd say what you are requesting is pretty tricky. There are radial selections in v9 (which is currently in beta), but I don't think we have that level of control over them. Yet.

             

            N.

            • 3. Re: Calculating sum of requests within a zip code radius
              Florian Zehl

              Hi Noah & Matthew,

              Thanks for that link. First, I'm using version 8.1.6

               

              I can give you a mockup for one sepecific example. You see on the first screenshot "current view" all the zip codes within the radius of 20 km with the total of requests for the respective zip code. What I want is the result which you can see it on the second screenshot "favorite view". I just want to see the entered zip code and the total of requests, in this case it is 243.

               

              Cheers

              Florian

               

              current view.JPG

              favorite view.JPG

              • 4. Re: Calculating sum of requests within a zip code radius
                Florian Zehl

                So what I really want to know is why Tableau ignores the filter of the table calculation for calculating the total of requests. One example for zip code 14469 and a radius of 20km (see also the screenshot): Obviously the total of requests is 49 and on the status line Tableau shows it correctly (yellow marked) but right in the table the total of requests is incorrectly based on all data from data source. Is there any solution for that issue?

                 

                pic.JPG

                • 5. Re: Calculating sum of requests within a zip code radius
                  Noah Salvaterra

                  This result has to do with order of operations in Tableau. Table calculations happen before filters based on table calculations. Try making a filtered version of your calculation:

                       If  [Radius Filter] Then Sum([Requests]) End

                  Using a the same partitioning as [Radius Filter]. Since that formally excludes stuff outside the radius it should do the trick, unless Tableau complains about the grand total anyway.

                   

                  Let me know if that works.

                   

                  N.

                  • 6. Re: Calculating sum of requests within a zip code radius
                    Florian Zehl

                    As you said I created the new field "Filter Requests" based on this formula: IF [radius filter] THEN SUM([requests]) END

                     

                    Then I used the created field for my filter. It seems this is also a filter field for table calculations as there is a triangle beside the name. However it now shows other zip codes than before and the total of requests are still all requests of the data source. What do I actually wrong?

                     

                    Let's have a look on the result without any change of the radius and zip code in the filter:

                     

                    pic2.JPG

                    • 7. Re: Calculating sum of requests within a zip code radius
                      Noah Salvaterra

                      Instead of applying this new calc as a filter, try using it to replace sum([Requests]) on the text shelf. Make sure this uses the same compute using as radius filter.

                       

                      I'm just guessing btw, it is difficult to work from screenshots, particularly when they don't even show the sidebar. There are a lot of data structure issues that could come into play that I wouldn't be able to anticipate. The ideal approach is to include a twbx (tableau packaged workbook) with your question that demonstrates the relevant data structure, but excludes or anonymizes any confidential data. That way helpers can tinker with it and provide an example of a solution.

                      • 8. Re: Calculating sum of requests within a zip code radius
                        Florian Zehl

                        I initially attached the workbook at the beginning of the discussion, however I now attached the latest version of the workbook again. I replaced sum([Requests]] with the new calc (Filter Requests) and added the table calculations for showing just the zip codes within the radius. But still the same, I got all the requests from the data source and not within the radius. You can also see it on the screenshot that the total of requests are based on all zip codes.

                        pic3.JPG

                        Could you have a look right in the workbook?. Maybe there is just a simple step I forgot and you can show me

                         

                        Thanks for your help Noah!

                        • 9. Re: Calculating sum of requests within a zip code radius
                          Noah Salvaterra

                          Hi Florian,

                           

                          To understand what is going on here requires some understanding of how Tableau computes totals. Joe Mako shared an nice perspective on this with me and it is a very helpful perspective for this example, so I will do my best to paraphrase (I pinged him just in case I got something wrong). Think of the grand total as a separate worksheet, where the only difference is that the dimension you are totaling across isn't present. In your situation, that means zip code isn't there. The problem with that your calculations, in particular the filters, depend on the full granularity of zip code. So what happens if you remove zip code from the view? If zip code isn't there, then the filter aren't meaningful since the distance in the aggregate becomes the distance between the average location and the average location, which is zero hence you get the total from the whole dataset.

                           

                          My approach to forum questions sometimes reminds me a bit of the tv show Mythbusters. They try to replicate an urban legend faithfully, and if that fails then they do whatever they need to in order to get the result. So I tinkered with your sheet for a while and couldn't seem to get the desired result with your existing structure, so I started looking for a hammer to squash the problem. At first I thought it might be a data blending issue, since blending can certainly introduce complexity and unexpected problems. So I formally joined the two tables. I didn't find that to be a magic bullet, but can't rule out that the blend is complicating things so I left this connection as part of the workbook. To get this result I had to utilize a trick that I really dislike, it is placing 2 worksheets next to each other on a dashboard in order to simulate the experience of a single table. You already had the table for the individuals, so I just needed to create a sheet for the grand total. A window_sum of the filtered version of sum([requests]) did the trick here, across zip. Note the filters, the filtered requests calculation and the window sum all require zip code, but we don't want to see it in the grand total, so it was moved to the detail shelf.

                           

                          My dislike of the concatenating sheets together workaround is so deep that I couldn't manage to hit add reply on this without finding a more palatable solution. For that I used a scaffold datasource, that is a datasource that is created with the structure needed for a particular layout which can function as a primary datasource, then any required measures can be blended in from the originals. In this case the scaffold seems a bit silly, because it is a list of all the zip codes plus one extra row, "Grand Total" by placing a row for grand total we have a place to hang the value of the window_sum table calculation, but it isn't Tableau's grand total, so it need not play by the same rules. I also created a row type field, which could have been calculated just as easily, but it is handy if you'd like an extra line between your table and the grand total (as with usual grand totals). Note, I created an explicit scaffold datasource because it seemed better for demonstration, but if you've got a zip code you're willing to throw out (for example if there is a row without location data or requests) it could be re-aliased to serve as the grand total row.This solution worked nicely, and gets everything on one sheet, but I have left both approaches in the attached workbook so you can choose the one that works best for your situation.

                           

                          Let me know if this helps or if you have any questions. There are definitely some subtle issues in play here.

                           

                          N.

                          • 10. Re: Calculating sum of requests within a zip code radius
                            Jonathan Drummey

                            When Noah gets stumped, then there's something really interesting going on.

                             

                            The problem with the table calc solution is that the Grand Total computation really *is* a completely separate computation and that extends to data blending. In the Grand Total, the zip_code dimension isn't present so the blend on zip_code goes away and the blend results are coming from that total. Here's an example where I duplicated the table_new_version view and took zip_code out of the view and changed the radius filter calc to address on Table (Across) (i.e. all the data):

                             

                            2015-03-24 12_42_33-Tableau - requests_radius_NS.png

                            The same results in both the detail row and the Grand Total indicate that Tableau is doing the same computation in each.

                             

                            There are a couple of additional solutions for this besides the ones that Noah put together:

                             

                            We can take the information I presented above and conclude that the Grand Total computation needs the zip code in its level of detail in order to work. Therefore, we could duplicate the zip code dimension in both primary & secondary, add the zip code copy to the LOD Shelf (the duplicate dimension will then be available in the Grand Total computation, see http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/ for details), adjust all the calcs so they work properly in the detail rows and Grand Total, and then create a (final) new nested calc for the sum of requests that will work across the higher level of detail inside the grand total so we only get one non-Null value in the Grand Total, and finally turn off mark stacking. That's painful enough that the work to just do an actual join might just be easier.

                             

                            The other solution is to enable retrieving the lat/long associated with the chosen value without having to resort to a table calculation. One possibility there would be to load up the parameter value with a string that had the lat/long and decompose it, but that is static and complicated to set up. Another is to use an additional blend, in this case a duplicate connection to the geo_data data source, and filter it on zip_code so it returns a single value. This second source can then be used to get the lat/long for the selected zip code. Then no table calcs or static parameters are necessary, the distance computation can be done using regular aggregates across the two blends, the grand total "just works" and the range filter for distance dynamically updates on the map. See demonstration #3 in Creating a Dynamic “Parameter” with a Tableau Data Blend | Drawing with Numbers for more details, I set up an example using this data in the "blend jtd" worksheet in the attached:

                             

                            2015-03-24 12_49_46-Tableau - requests_radius_NS.png

                             

                            Jonathan

                            2 of 2 people found this helpful
                            • 11. Re: Calculating sum of requests within a zip code radius
                              Noah Salvaterra

                              Nice work Jonathan! I like to see multiple solutions, particularly when one proves to be as elegant as yours or one is as miserable as concatenating worksheets together, but you changed the problem a bit. What if a discrete filter with the values 20km, 50km, 100km, 200km, 300km or all of Germany is part of the requirements? Could we still use the blending approach?

                               

                              I think Florian's original workbook was in version 8.1. I wanted to work through this approach, so I recreated the new sheets without upgrading. Otherwise there isn't anything new in this attachment.

                               

                              N.

                               

                              P.S. Be sure to tune in for Jonathan Drummey's Think Data Thursday this week, I heard a rumor this problem might make an appearance:  TDT: Data Blending - Left Join or not? - presented March 26, 2015

                              1 of 1 people found this helpful
                              • 12. Re: Calculating sum of requests within a zip code radius
                                Jonathan Drummey

                                Thanks for making this an 8.1 workbook!

                                 

                                The blending approach can work, however since we can't put a discrete regular aggregate on the Filters Shelf (grumble grumble look for the Idea and vote it up), we'd have to use a continuous pill (maybe via an intermediate calc that binned the distances) or a parameter+calc.

                                 

                                Jonathan

                                1 of 1 people found this helpful
                                • 13. Re: Calculating sum of requests within a zip code radius
                                  Noah Salvaterra

                                  I tried those before I asked. No luck. It could be binned via a calculation, but since the pill still needs to be continuous so it doesn't change the presentation (it is just secretly bucketed). And the parameter+calc could get a boolean for example, it wouldn't let me drop it on the filter shelf at all, and if you put it on rows say and select keep only, then it creates a static filter on zip code. You could hide the values you don't want, but then we end up in the same mess with the totals. I think this thread has stumped me twice in one... oh never mind, I got it.

                                   

                                  N.

                                  1 of 1 people found this helpful
                                  • 14. Re: Calculating sum of requests within a zip code radius
                                    Florian Zehl

                                    What a nice discussion here This is really helpful, many thanks to both of you guys. I already had a look on your different approaches and it's really interesting to see the ways you work with the issue. So I'm really thankful and I'm now much more aware of this topic.

                                     

                                    By the way I already registered for Jonathan's web session for tomorrow "Data Blending - Left Join or not?". It sounds really interesting for me especially since there is a rumor that this problem might make an appearance

                                     

                                    Again many thanks!!!

                                    Florian