1 2 3 4 5 Previous Next 72 Replies Latest reply on Apr 17, 2019 1:29 PM by Mrinalini Kulkarni Go to original post
      • 30. Re: Concentric circles
        Shawn Wallwork

        Ahhh, an oldie AND goodie! First you need to know I got the same result you are getting when I tried to build this from scratch. I started with Richard's (the guru of this technique) workbook and I copied and pasted every single calculation into my workbook, no go. So I checked and double-check to make sure every pill on every shelf was exactly, I mean exactly the same, and still no go.  I never got a 'from scratch' workbook to ever work; so I just went the modification route!? So you need to know that the workbook posted above is the great-great (maybe) great grand child of Richard's original work. I know it doesn't make any sense, but I live in the Southwest and the native spirits are powerful things around here so you never know...

         

        Really, you've got two choices, adapt the one above, or get Richard to help you track down the anomaly that's causing the problem. Sorry I couldn't be of more help.

         

        --Shawn

        • 31. Re: Concentric circles
          Richard Leeke

          I remember that issue from when I was first putting that example together - it took me ages to figure this out.  I really should have mentioned it when I first posted the example.

           

          It's actually just a really, really misleading error message. The real issue is that the MS JET data connection does not support the ATAN2() function - so you need to use a data source that does. Creating an extract is the simplest cure.

          1 of 1 people found this helpful
          • 32. Re: Concentric circles
            Gregg Thomas

            Thanks a lot - i used an extract and it's now working.  Appreciate the help!

            • 33. Re: Concentric circles
              Brian Smith

              Richard could you please post your actual code so I could see. I tried replicating it and I have an issue w/ excel so had to load a tbl and cant get it to work right in custom sql

               

              thanks

              • 34. Re: Concentric circles
                Richard Leeke

                On my phone at the moment so can't hunt out old workbooks, but I think the workbook I posted back at posting number 11 on this thread should have the sums you are after. If not, can you be more specific about what you are stuck on and I'll see if I can find an old example.

                 

                I notice you mentioned problems with Excel.  This approach doesn't work with Excel because the MS JET engine doesn't support all the trig functions you need.

                • 35. Re: Concentric circles
                  Brian Smith

                  oh i wondered on the excell issue...

                   

                  I loaded the attached sheet (site names replaced) into a table as well as the Circle data.

                  I got the gist of what you were saying but cant get the code to work right in custom sql.

                  Select

                  distinct


                  z

                  .primary_city


                  ,

                  z.county


                  ,

                  z.state


                  ,

                  z.timezone


                  ,

                  z.type


                  ,

                  z.zip


                  ,

                  z.latitude


                  ,

                  z.longitude


                  ,

                  [Circle] as null


                  ,

                  [Degree] as null



                   


                  FROM [DataAnalytics].[dbo].[ZipCodes$] z



                  Where

                  z.state = 'NJ'



                  UNION

                  ALL



                  Select

                   


                  z

                  .latitude as Circle_Center_Lat


                  ,

                  z.longitude as Circle_Center_Long


                  ,

                  c.Circle


                  ,

                  c.Degree


                  ,

                  z.primary_city as null


                  ,

                  z.county as null


                  ,

                  z.state as null


                  ,

                  z.timezone as null


                  ,

                  z.type as null


                  ,

                  z.zip as null



                   


                  From

                  Dataanalytics.dbo.[Circles$] c

                   

                  I subsituted some of the fields you guys had used. I am trying to create circles for the radius around a bunch of NJ sites but segmented so i can see the 10 mi the 20mi and 30mi etc.

                  Thanks so much for your help Richard.

                  • 36. Re: Concentric circles
                    Richard Leeke

                    I've had a quick look but I don't have time to take it through to a working example. I've attached where I got to before my priorities took over.

                     

                    There is a modified version of your spreadsheet in which I've added a third sheet with the locations of three centres (I just picked some random latitude and longitude values out of your locations sheet to demonstrate the idea.

                     

                    There is also my first stab at what the custom SQL should look like - but I literally just typed this in and haven't even opened the spreadsheet and tried connecting with that, so I'd be amazed if it works quite like that.

                     

                    Once you get the SQL going you will need to create a data extract before you can add the calculated fields for the latitude and longitude of the circle boundary points - that is because of the lack of trig functions in the JET engine.

                     

                    Hope that gives you a prod in the right direction.

                    • 37. Re: Concentric circles
                      Jonathan Drummey

                      test post, please ignore.

                      • 38. Re: Concentric circles
                        Louise Prister

                        Can you give me a hing of how to calculate the Latitude and Longitude on a radius around every one of my 7000 vendors.

                        Is there a formula I can use?

                        Thanks

                        Louise

                        • 39. Re: Concentric circles
                          Volodymyr Holomb

                          Hi there, Richard!

                           

                          Thank  you for those neat solution for concentric circles on the map. But your further help is still needed.


                          I want to laid the circle over a filled map with some countries on it in exactly the same manner it was done inRichard Leeke's New Version of (Concentric) Circles. But I failed to open this workbook with neither Tableau Public 9.0 nor Desktop 9.1.


                          The problem looks like this

                          mistake.png

                          Please, give me an idea of how to join two tabs/sheets in enclose workbook on one map.


                          Waiting for your suggestions.


                          • 40. Re: Concentric circles
                            fan.zhang.6

                            Hi Jason,

                             

                            I was wondering if you have figured out a solution to overlapping the shaded circles around the plant with your customer locations.I have an almost identical problem as yours, and was able to get to the following map using Richard's suggested method. However, as you can see, I couldn't get rid of the hollow circles (circle points around the plant) and after I applied dual axis, it formed annoying boundaries of the shaded area.

                             

                            Would Appreciate any inputs as to how to get rid of the hollow circles.

                             

                            Thanks,

                            Fan

                            Map1.png

                            Map2.png

                            • 41. Re: Concentric circles
                              naresh.suglani.0

                              Is there any way to add a dual layer map on these concentric circles if the lats and longs of those are in a different file? I see that radius here to plotted using circle lats and longs so adding a second instance of lat on the shelf to create a dual layer wouldn't work.

                              • 42. Re: Concentric circles
                                kristian fraser

                                Hi Richard Leeke

                                 

                                Some 8 years after your responses on this thread i happen to be doing some research on this very topic. I have read in detail and reverse engineered (as best as i could understand it any way) the circles files you have added as well as Robert Mundigl Clearly and Simply site discussing site catchment analysis  (so good, i also looked through many other things, so much learning to do). This is to solve a task i have been given in a project which is "count the number of homes within a defined radius from defined central locations".

                                 

                                I have managed to add and get your circle viz working (looks so good, thank you for your effort on these), my obstacle is that i am unable to find a way to count the number of households that fall within the various radius. The central points are shops, the other HH types are households in a small area as test data.  A follow on question i am anticipating is that stakeholders will want to see the segmentation attributes that have been applied to these household ID's which are the lowest level of detail.

                                 

                                I was wondering if there was an opportunity to review my attached workbook as i don't know where i made a mistake...

                                 

                                Appreciation in advance and thanks again for this post and your effort to date at the very least.

                                • 43. Re: Concentric circles
                                  Richard Leeke

                                  Hi Kristian

                                   

                                  No time to look at it right now, but I'll happily have a look when I get a chance - probably tomorrow.

                                   

                                  One general comment. Since the time of this thread Tableau has added significant extra support for spatial data, which for some classes of problems can provide a much better solution. Though to use that you need to get your data into a SQL Server database, which is the only platform for which Tableau has spatial support (yet - Postgres support is coming soon, too).

                                   

                                  As a bit of background reading, you might like to have a look through this thread started by Wilfred Eze recently in which I helped him work through some analysis using the SQL Server spatial functionality. Wilfred's requirement was quite a bit more complicated than yours, I think, but it should give you an idea of what is possible (and the effort involved). One of the Tableau folk, Sarah Battersby, who also posted a lot on that thread subsequently distilled it down into a pair of blog posts, here and here. Those are probably a better bet than reading the whole thread - there were quite a few false starts and roadblocks along the way.

                                   

                                  Richard

                                  • 44. Re: Concentric circles
                                    kristian fraser

                                    Thanks again Richard, i will look through those posts today.