10 Replies Latest reply on Sep 25, 2019 8:14 PM by Soumitra Godbole

    Plotting Regions with Lat/Long in a Calc Field

    Marc Levy

      In my data I have a field for region.

       

      Rather than adding Latitude & Longitude to an Excel file or adding it to my database, can I create a calculated field with the latitude and longitude and then plot them on a map?  Looking to plot a circle on a map representing a region.

       

      For instance,

       

      Regions:

       

      MDW   Lat=  35,  Long -110

       

      BAC  Lat= 45, Long -22

       

      AVC  Lat=68, Long -7

       

      Appreciate if you could show the solution in a sample workbook with these regions.  Again, not looking to have the Latitude/Longitude data in a Excel file but just in a calculated field.  Not sure it's relevant, but as a side note, some of my regions fall inside the United States and some fall outside it.

       

      Thanks,

      Marc

        • 1. Re: Plotting Regions with Lat/Long in a Calc Field
          Michael Gillespie

          Well, you can't have a calculated field without having a field to base the calculation on. 

           

          You need 2 fields.  Can't do it in one, as you need lat & long to be separate columns.

           

          So, for latitude:

          IF [Region]='MDW'

          THEN 35

          ELSEIF [Region]='BAC'

          THEN 45

          ELSEIF [Region]='AVC'

          THEN 68

          END

           

          And something similar for Longitude.  Then you set the geo role for each field to the corresponding value.

           

          You'll end up with a point on a map.  Is that what you're looking for?

          • 2. Re: Plotting Regions with Lat/Long in a Calc Field
            Jim Dehner

            good morning

             

            in 2019.2 Makepoint was introduced - it can be nested in other calculation or used as a stand alone to  create a single point

             

             

             

            it would return this for your first point as an example

             

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Plotting Regions with Lat/Long in a Calc Field
              Marc Levy

              Hey Jim,

               

              Thank for the reply.  I'm curious about this MAKEPOINT calculation.  How would this work with multiple points on a map?  Let's say I have three regions (in my actual data set I have many, many more).   How would this work?  Getting an error message (below) when putting in more than one region into the calc field.

               

              • 4. Re: Plotting Regions with Lat/Long in a Calc Field
                Jim Dehner

                looks like you are not up-to-date with 2019.2 (at least )   -   

                these 3 were introduced at that time

                 

                 

                 

                if you have only 3 I would create a case statement and load them that way

                you can use them in any nested calc or LOD but that said you need to upgrade

                 

                Jim

                • 5. Re: Plotting Regions with Lat/Long in a Calc Field
                  Marc Levy

                  Thanks, Jim.  I upgraded my version of Tableau and was able to get the MAKEPOINT function and render the points on the map.

                   

                  Many thanks,

                  Marc

                  • 6. Re: Plotting Regions with Lat/Long in a Calc Field
                    Jim Dehner

                    Great - they are good additions - Distance makes it easier to calculate the "Crow fly" distance between 2 points

                     

                    Glad to help out

                     

                    Jim

                    • 7. Re: Plotting Regions with Lat/Long in a Calc Field
                      Marc Levy

                      Hey Michael,

                       

                      I created these two calc fields, and changed one of the geo roles to Longitude and one to Latitude.  However I'm not able to get the points to render on a map.

                       

                      When I drag the newly created calc fields to rows or the level of detail shelf, "SUM" is showing before the name of the field.

                       

                      Could you show me an example workbook and/or expand your answer on how to do this?

                       

                      Thanks,

                      Marc

                      • 8. Re: Plotting Regions with Lat/Long in a Calc Field
                        Marc Levy

                        Nuts, I can't implement this "MAKEPOINT" solution because my version of server is older than 2019.2.  I'll have to find another solution.

                        1 of 1 people found this helpful
                        • 9. Re: Plotting Regions with Lat/Long in a Calc Field
                          Soumitra Godbole

                          Hello again Marc,

                           

                               Hope you're having a wonderful day. The battle isn't lost as yet and here is an interesting and

                          simple solution using an earlier version 10.5 (prior to 2019.2). I wish to Make a Point  without having

                          to use the function Makepoint !

                           

                          Please note that  the current solution provided above works perfectly using the newer version (2019.2 &

                          2019.3). The following solution is for prior versions and the explanation is rated U (for all audiences).

                          Also the advantage of this method is that you have no restrictions on the number of points as long as

                          the dummy field has greater than or equal to the number of points you need to show on the map.

                           

                          Dashboard.png

                           

                               Here is the trick, using the simple superstore workbook (directly connecting to the excel workbook).

                          I looked for a dummy field you probably may not need to use as a filter on the dashboard and found

                          "Shipping Mode". Really, you can use any random field that is of little importance and create this calculated

                          field. Another option is doing a cross join to an excel file with the Latitude & Longitude data.

                           

                               Luckily it had more than 3 values (actually 4) so with the following calculated fields you can easily arrive

                          at the solution and probably do a lot more like varying sizes based on numbers, use Squares or alternate

                          shapes etc. and add a little color.

                           

                          Formula 1 - Location No.png

                          Formula 2 - Latitude.png

                          Formula 3 - Longitude.png

                          Formula 4 - Label.png

                          Also remember to set the Longitude (Calc) & Latitude (Calc) fields to

                          Geographic Role as shown below:

                           

                          Geo Role.png

                           

                          The above screenshot is only for Longitude, but you will have to do it for

                          Latitude as well. Well with all this I regret that I have nothing else to say.

                           

                          Question:  Is there any catch to this method ?

                          Answer:     Absolutely! This solution can fall apart if you place the "Ship Mode" field

                                             into the filter shelf. But for the problem, I am assuming you will not need

                                             it . Another option is to replicate and use this datasource for the map.

                           

                          Hopefully you will have some other id field that you can use to create the above

                          calculated fields (it does not matter how many IDs you have as your problem only

                          requires 3 that will be allocated to the 3 Regions and the others will be Null).

                           

                          The best part about Tableau is that it allows you to come up with completely new ways of

                          solving problems in addition to the existing ones. Here the importance is given to the idea

                          and how it is implemented rather than using it as a mere tool to achieve the result i.e. more

                          like the "Mind over Matter" approach.

                           

                          I have attached the the twbx file below. I sincerely hope you find this alternate solution useful

                          in addition to being entertaining and it answers your problem. Also let me know if you have

                          any questions or would like any changes or further modifications. Best Wishes !

                           

                          Sincerely,

                          Soumitra

                          • 10. Re: Plotting Regions with Lat/Long in a Calc Field
                            Soumitra Godbole

                            Adding to the earlier solution, one can use the suggested approach, to try and show the

                            NY locations used in the 1985 classic film BREWSTER'S MILLIONS (starring Richard Pryor,

                            John Candy, Rick Moranis and others). Using the page shelf you can also create some fun

                            animation showing locations appearing on the map.

                             

                            Once we have the Latitude and Longitude values for the 8 locations shown below,

                            we can use the  earlier technique to get a trail (using the Page shelf in Tableau) to

                            display the locations shown below.

                             

                            Also the thought of spending 30 Million (USD) in 30 days to inherit 300 Million would make a

                            cool Balance Sheet visual (time series). This would be a fun weekend activity for those interested.

                            Enjoy !

                            Locations.png