14 Replies Latest reply on Jun 15, 2018 6:33 PM by Don Wise

    Plotted Addresses on top of a Heatmap (Dual axis)

    Christopher Ray

      Hello everyone,

       

      I've been reading so many threads for a few days now trying to figure this one out on my own and I simply cannot break through.

       

      My objective is to create a heatmap by Zip code, with a filter to look at individual measures. Sheet 2 on my attached Excel sheet demonstrates this. The intention is that you will view the heatmap by measure. I.e., what is the county's performance by Measure #1 (Access to Care), what is the county's performance by Measure #2 (Medication Adherence), etc.).

       

      Then, I'd like to overlay specific address points onto the map, with some filters by type of location (Pharmacy, Provider, Hospital, etc.). This seems like such an easy task, but I cannot for the life of me get it to work.

       

      I'll be honest in that I'm really not even sure how my data should be structured and where/when I need to generate my own Lat/Long coordinates. I have Lat/Long coords for the specific addresses, but I don't know how to use lat/long coords for Zip codes for the heatmap (even I even need to!).

       

      The attached spreadsheet contains my data in it's most raw format. I'm really looking for some guidance to 1) structure my data in the way it needs to be for import to Tableau, and 2) how to generate a dual axis map to display both a heatmap and specific address points.

       

      THANK YOU so much in advance for any help you can provide.


      EDIT: I've also attached a packaged workbook with a dashboard showing the two maps I'm looking for separately. I simply need to combine those two.

        • 1. Re: Plotted Addresses on top of a Heatmap (Dual axis)
          Don Wise

          Hi Christopher,

          Please see attached 2018.1 workbook.  The Zip Codes were of different format (string vs. number) so that was part of the problem.  Once that was accomplished the two sheets could be joined together in the data pane as shown below on Zip Code.  From there it was a matter of dragging GEO LAT and GEO LON to the rows/columns and adding the point values needed to display the points.  After that, dragging a new instance of GEO LAT next to GEO LAT again, creating a dual-axis map, removing all dimensions and then adding in the dimensions from only from "Heatmap" and chaining marks from circles (points) to Filled Map and coloring thereafter. 

           

          I think your "Heatmap" data is likely to need some work in order to filter appropriately...I did a Left-join simply to show all points vs. limited amount of zip-codes from Heatmap.  You can just easily change that to Inner Join to limit the view until you get the rest of your data in the "Heatmap" sheet.

           

          Hope it helps and if it is correct please mark this thread as correct. Thx, Don

          Screen Shot 2018-06-06 at 9.08.28 AM.png

          Screen Shot 2018-06-06 at 9.23.18 AM.png

          1 of 1 people found this helpful
          • 2. Re: Plotted Addresses on top of a Heatmap (Dual axis)
            Christopher Ray

            Hi Don!

             

            I really appreciate you taking this up and helping me out. It looks like you're running into the same issue I was at one point. Only the Zip codes that have a plotted address are showing up in the heatmap. For example, 11742 does not show up in the heatmap.

            • 3. Re: Plotted Addresses on top of a Heatmap (Dual axis)
              Don Wise

              Hi Chris

              I’ll take a look when I get back later this afternoon!

               

              Sent from my iPhone

              • 4. Re: Plotted Addresses on top of a Heatmap (Dual axis)
                Don Wise

                Hi Chris,

                 

                Yes, you're correct that 11742 does not show up in the heatmap. 

                 

                The reason for this is it doesn't have a corresponding value for Pharmacies in GeoCodeResults table to join on from the Heatmap table.  So it'll show up as a NULL value when a merge of the data occurs.  There are 125 pharmacies under GeoCode results and there are 125 marks on the map (1 of them being the NULL value). 

                 

                You'll need a pharmacy that falls in the 11742 zip code, geo-coded with lat/lon entered into the GeoCodeResults table in order for the two corresponding sets of data to be visible.  Hopefully that makes sense?

                 

                Heatmap.png

                 

                GeoCodeResults.png

                It doesn't matter if the Join Type (Inner, Left, Right, Outer) is changed.  The results will be the same:

                Screen Shot 2018-06-06 at 3.10.47 PM.png

                • 5. Re: Plotted Addresses on top of a Heatmap (Dual axis)
                  Christopher Ray

                  Hm, so if I don't have any pharmacies in that zip code, the heatmap can't be visible? Seems strange/not ideal.

                   

                   

                  (Sent by my Android device)

                  • 6. Re: Plotted Addresses on top of a Heatmap (Dual axis)
                    Don Wise

                    Hi Christopher,

                     

                    The only other thoughts I have on the matter is this reference (Dual Axis Mapping - Many Ways ) but it doesn't exactly reference what you're trying to do. 

                     

                    I know that the 2018.2 version of Tableau will be more robust and allow for a layer of one type of geography to be dual-axis on another type of geography without need for a join of the data sets.

                     

                    Let's ping Sarah Battersby to see if she has some creative ideas on what you're trying to do here, which is to show all zip codes with measures as filled map and dual-axis pharmacies with lat/lon points, regardless of whether either side has matching data or not.  Hang in there!

                     

                    Thx, Don

                    • 7. Re: Plotted Addresses on top of a Heatmap (Dual axis)
                      Don Wise

                      Hi Christopher,

                       

                      Ok, I took another stab at it and got the below result.

                       

                      Not ideal from an analysis standpoint, but it will get you your polygon/zip code, however on hover it will not display any results such as your measure of performance because of the aforementioned reasons. 

                       

                      Attached is a revised workbook.  Please see Map2.

                       

                      Hope it helps and we'll see if Sarah weighs in too.  Thx, Don

                      Screen Shot 2018-06-06 at 4.49.26 PM.png

                      • 8. Re: Plotted Addresses on top of a Heatmap (Dual axis)
                        swaroop.gantela

                        Christopher and Don,

                         

                        This may not work for your true dataset if it is large,

                        but I was wondering if you could try a join on a calculated field of 1.

                        Then there would be no worries about having a pharmacy in 11742.

                         

                        The Performance would need to be calculated as a Level of Detail

                        expression because of the many duplicates due to the join:

                         

                        { FIXED [Zip Code],[Measure]:MIN([Performance])}

                         

                        which will be summed when placed on the map.

                         

                        I'm not sure what effect this full join will have on your pharmacy dots,

                        because there are likely multiple dots on one spot.

                         

                        Please see the workbook attached in the Forum Thread.

                        272273map.png

                        • 9. Re: Plotted Addresses on top of a Heatmap (Dual axis)
                          Don Wise

                          Hi Swaroop,

                           

                          Your solution would be a really nice option for Christopher to consider! 

                           

                          Thank you!  Don

                          • 10. Re: Plotted Addresses on top of a Heatmap (Dual axis)
                            Sarah Battersby

                            Is this what you are trying to get - all of the zip codes (with some of them having performance measures) and all of the points?

                             

                            If you're using 2018.1 you can take advantage of the updates to dual axis mapping to combine generated and non-generated lat/lon.

                             

                            I just did a full outer join on 0=1 so that I ended up with all rows for both datasets, and then created a new zip code calculated field that had a value for each row (so either the zip code of the point, or the zip code with the performance measure).  Drop onto a dual axis map, and done!

                             

                            2018.1 workbook attached.

                             

                            Or, if that isn't what you were trying to create, let me know and I'll see if I can figure something out.

                             

                            -Sarah.

                            Tableau Research

                            1 of 1 people found this helpful
                            • 11. Re: Plotted Addresses on top of a Heatmap (Dual axis)
                              Don Wise

                              Thanks Sarah for responding with your solution!

                               

                              Hopefully Christopher will reach out and let us know what worked for him.  Don

                              • 12. Re: Plotted Addresses on top of a Heatmap (Dual axis)
                                Christopher Ray

                                Thank you for the additional idea! I will absolutely give this a test next week and get back to you.    

                                • 13. Re: Plotted Addresses on top of a Heatmap (Dual axis)
                                  Christopher Ray

                                  Hi Sarah Battersby

                                   

                                  I wanted to thank you and Don Wise again for continuing to help me out. In the attached version, I have a functioning version. It is mostly doing what I want, with a few non-ideal features. For example, I now have a performance filter so you can flip between different measures and the underlying heatmap will change for the chosen measure. I also have a filter for "Hub" and "Entity Type". These two filters are for the plotted points. One thing I don't like is when yo use those two filters, if there aren't any plotted points in the zip code, the heatmap won't light up that zipcode, even if it has performance. However, if that's the way it is, I can get past that.

                                   

                                  EDIT: I'm also noticing that my filters will often have a "Null" option for the rows in the joined data that are performance (zip code) only with no plotted addresses. How can I remove the null option from the filters as a choice?

                                   

                                  However, another thing I'm unsure of, if I wanted to give users the option to disable the heatmap altogether and only show the plotted points, how could I do that?

                                   

                                  Thank you!

                                  • 14. Re: Plotted Addresses on top of a Heatmap (Dual axis)
                                    Don Wise

                                    Hi Christopher,

                                     

                                    Please see below screenshot for what I found regarding the Null concern.

                                    Null Values.png

                                     

                                    On the other concern about the two filters displaying only those zip codes that only have a geo-point, that behavior is what's currently expected in this version of Tableau.  I believe what you're looking for, with the ability to display the heat map by zip code as a filled map independently and regardless of whether there's an available point or not, and inclusive of your desire to have the ability to turn off points and/or turn off the heat map independently of each other, might be something that could be done in the next release of Tableau.  We'll have to wait and see...but then you'd have to ask, how would the folks filter appropriately if they were independent of each other?

                                     

                                    Currently, I haven't seen the ability to independently control the display of the two maps that are layered together. A workaround, would be something like this, which is to go from a filled map to circles, but on the dashboard: https://interworks.com/blog/smarx/2016/07/07/playing-maps-tableau-making-mapping-really-interactive

                                     

                                    Thanks, Don