4 Replies Latest reply on Mar 12, 2012 9:32 AM by John Mogielnicki

    Show Max Value Within Category on Map

    John Mogielnicki



      I'm working on visualizing survey data geographically on a map, and I've run into a problem.  I'm trying to create a map that displays the most prominent age segment in any given zip code, and I can't figure out how to do it.  I've attached some dummy data and a packaged workbook that demonstrates the issue.  Right now I have the data displayed on the map by zip code as a pie chart.  I want to be able to show for each of these zip codes only the survey age range with the highest count of applications.  So, for example, zip code 83263 would show up as only "56 and over."


      Ultimately, I'm looking to create a filled map that shows the most prominent age segment per zip code.


      Can anyone point me to the correct table calculation for this?





        • 1. Re: Show Max Value Within Category on Map
          Shawn Wallwork

          John, let me admit upfront, having hit a major deliverable (with Richard's help 3! times) I cracked open the Chardonnay. So be forewarned the attached in an 'infused' workbook. It looks like this:



          And no short of 'rolling your own' lats/longs you won't be able to get the labels to land on the ZIP code areas any better than you see here. (But this correction has been hinted at as 'coming soon'.) Hope this gets you going on the right track.



          1 of 1 people found this helpful
          • 2. Re: Show Max Value Within Category on Map
            John Mogielnicki

            Hey Shawn, I really appreciate your help, and that is SO close to what I'm looking for.  The difference is that I was hoping to get the field "Survey Age" on the color shelf, so that I could look at a wider geographic area and see at a glance where the pockets of certain age ranges are located.  Does this make sense?  I tried using your workbook and moving the Aggregated Count field to the size shelf and survey age to the color shelf, but that creates the same issue that I had before, that the colored bubbles overlap each other.  Instead, I want each zip code to show one bubble that is the color of the most prominent survey age.

            • 3. Re: Show Max Value Within Category on Map
              Richard Leeke

              You need to use window calculations to do this, as in the attached.


              Note that I have skirted over the issue of how to decide in the case of a tie. I've used age as the tie breaker - i.e. I'm using the oldest category that had the highest count in the event of a tie.


              I vaguely recall Ross Bunker of Tableau demonstrating some clever way of handling a tie in cases like this in a forum post when Table Calcs first came out in version 6 - so if it matters in your case you could go hunting for that old thread...

              • 4. Re: Show Max Value Within Category on Map
                John Mogielnicki

                Thanks Richard!  It looks like that's working.