7 Replies Latest reply on Oct 21, 2014 8:22 AM by Kevin Andrist

    Table calculations on radius filtering map

    Kevin Andrist

      I've been struggling with understanding what I believe is a partitioning-addressing problem.  As I've researched P&A I've found a number of forum threads that have been helpful, but the struggle continues. It's somewhat comforting to read the likes of Richard_L, Jonathan_D, Alex_K, Andy_C, Matthew_L comment about not completely grasping table calculations.  I wonder where that puts me..yikes...

       

      I also enjoyed Richard's comment about only three people in the world ever really understanding General Relativity.  I thought I had it long ago in school..but a Winston Churchill quote comes to mind, "I had a feeling once about Mathematics, that I saw it all—Depth beyond depth was revealed to me—the Byss and the Abyss. I saw, as one might see the transit of Venus—or even the Lord Mayor's Show, a quantity passing through infinity and changing its sign from plus to minus. I saw exactly how it happened and why the tergiversation was inevitable: and how the one step involved all the others. It was like politics. But it was after dinner and I let it go!"

       

      Ok..the question(s):

       

      I have a couple versions of parameter-driven radius maps that I use, portions of one originated with Richard I think(thank you for the leg-up).  I came across another (see radius-filtering_original downld reference.twbx attached, author unknown) that has some aspects I like, if I can understand how to make some additions/changes and extend the functionality to an associated crosstab...and therein is where I'm have some issues.

       

      Attached is a reverse-engineered version (the *.tab & *.xlsx source file) of the reference *.twbx workbook.  Things like this help me learn & generalize various techniques.

      Q1)  If you look at the Map1 sheet you'll note the parameter 'zip code selector' has zip code 48043 selected.  You'll see no center point identified on the map (it'll be black).  Now set the zip code selector to 48044 or 48042..that's what it should look like.  The missing center point zip code (48043) is a choice in the zip code selector parameter list and yet the Highlight Central Zip Dimension calculation doesn't evaluate to TRUE.   Or the Distance Measure calculation(the distance between 48043 & 48043 = 0) fails to evaluate to equal zero. Zoom in, the selected 48043 zipcode mark isn't displayed on the map.   Why?  This is a specific example of many selected zip codes not displaying the centerpoint.

       

      Q2) I added a couple categorical filters to the xlsx file ("Population Category" and "US Region")  to simulate how I'd like to utilize the completed dashboard(categories could be done with groups in Tableau).  In my *.tab file I included a conventional worksheet ("Detail_Conventional xtab") to illustrate how I'd like the "Detail1" crosstab to function (in terms of post-map-radius-selection filtering).  The "Detail_Conventional xtab" filters down like any worksheet.

       

      Q2, Endpoint I'm after:  I'd like to select a zip code & mile-radius on Dashboard1, have the map reflect what I've selected, have the crosstab reflect the mile-radius selection(I have it doing both now), and then be able to filter the crosstab down (independent of the map) the same way I can filter down the "Detail_Conventional xtab" crosstab.  I think (?) I know how to get a population total of the filtered down crosstab selection(a window_sum calc), however I haven't tried it yet.  I've been trying a number of configurations for a couple days and researching partitioning/addressing I'm not there yet.  Any ideas?

       

      The Map2 worksheet is just an alternate map that I may use in the dashboard.  It uses the Population category for color rather than a Distance gradient.

       

      Forgive me if the answer is dumb-simple.  Sometimes I dive in only to miss the forest for the trees.

        • 1. Re: Table calculations on radius filtering map
          Kevin Andrist

          anyone?

           

          I'll continue working on it over the weekend..any ideas welcome..thx.

          • 2. Re: Table calculations on radius filtering map
            Kevin Andrist

            I've worked on this a bit more..still haven't arrived at a solution. 

             

            Anyone want to take a swing at it?

             

            thx..

            • 3. Re: Table calculations on radius filtering map
              Matt Lutton

              First, let me say you should be comforted by those comments.  Table Calcs are incredibly tricky to set up, in some scenarios, and they can easily be OVER complicated and confusing for users.  Only through the work of other users have I begun to understand the concepts involved with setting up Table Calcs.  One resource you may not have found yet is the TDT Video Library, which contains some helpful videos on Table Calcs in particular:  The specified item was not found.

               

              I think the problem may be you've uploaded several files (a twb, an Excel file, and a TWBX), and people aren't sure where to focus (that's my problem, anyway).  I'm only interested in opening the TWBX, and when I do, I only see one sheet called "Radius Filtering", which does not correlate with the text description you've written in your original question.  So, I'm just confused to be honest--is this the workbook and sheet I should be looking at?  If so, can you start with this sheet, and explain the problem occurring and what you're attempting to accomplish?  I'm just not clear on the goal, as it relates to the TWBX you've provided.

               

              Cheers!

              • 4. Re: Table calculations on radius filtering map
                Kevin Andrist

                Matt,

                 

                Thanks for taking a look.  My apologies for the confusion, I thought it was clear at the time..  Give me a few minutes and I'll take a look..and decode it or do a re-boot on attachments/description.

                • 5. Re: Table calculations on radius filtering map
                  Matt Lutton

                  No worries -- if I'm supposed to look at the Excel file as well, I can do that.  I'm just not clear on what I should look at/focus on here.  I tend to go with the TWBX, and based on that workbook, I was confused.

                   

                  I added some other comments about Table Calcs in general, and how confusing they can be.  Know that you are NOT alone. 

                  • 6. Re: Table calculations on radius filtering map
                    Kevin Andrist

                    Matt..OK, I'll explain.

                     

                    1) The attached *.twbx file is the original reference workbook I downloaded off a Tableau Knowledge Base article I ran across as I was researching this problem.  I don't know who authored it, but it's a good (BIG) step toward what I'm trying to develop.  It is only a reference.  I made no changes to this file.

                     

                    2) The attached *.twb file is my reverse-engineered version of the reference twbx file cited in 1) just above.  My "radius filtering_forum.twb" is pointed at (uses the datasource of) the attached Excel file, "Radius Filtering Data.xlsx". All my questions concern the *.twb / *.xlsx file pair.  I left them as separate files as it may be easier to work with / understand what I've done so far.  If you open just the *.twb file then this is all you need to look at.

                     

                    My *.xlsx file adds in categorical fields("Population Category" and "US Region")  that mimic the actual marketing/customer data & category-fields that I'm working with.  The zip code & population data here is just sample data as the actual data is proprietary.

                     

                    With that as background, then in Q1 I'm wondering why the centerpoint zipcode  doesn't display with some origin zip codes selected via the parameter "Zip Code Selector".  This same problem exists in the reference *.twbx file. This is a relatively minor, but annoying problem.  I thought as long as I was going to ask about Q2, I'd ask this one also.

                    Q1)  If you look at the Map1 sheet you'll note the parameter 'zip code selector' has zip code 48043 selected.  You'll see no center point identified on the map (it'll be black).  Now set the zip code selector to 48044 or 48042..that's what it should look like.  The missing center point zip code (48043) is a choice in the zip code selector parameter list and yet the Highlight Central Zip Dimension calculation doesn't evaluate to TRUE.  Or the Distance Measure calculation(the distance between 48043 & 48043 = 0) fails to evaluate to equal zero. Zoom in, the selected 48043 zipcode mark isn't displayed on the map.  Why?  This is a specific example of many selected zip codes not displaying the centerpoint.


                    In Q2, This is the core of the question. The description of what I'm after follows below(in blue). If you look at the dashboard and select a distance (at top of map) and origin zipcode(please do select a new one) you'll see everything works fine.  The map shows zipcodes from an origin at a mile-radius you selected..perfect.  You'll also note the Detail1 crosstab filters down to reflect the mile radius selcted on the map..perfect. Now try the filters on the right of the dashboard.  They don't work, meaning the crosstab doesn't filter down as you think it should.  that's the problem.  How should it filter?  Take a look at the "Detail_Conventional xtab" worksheet and try filtering it down using the filters on the right..that's how I'd like the filters to work on the dashboard.  The problem must be in partitioning and addressing...but I'm getting nowhere on working through a solution.


                    Q2) I added a couple categorical filters to the xlsx file ("Population Category" and "US Region")  to simulate how I'd like to utilize the completed dashboard(categories could be done with groups in Tableau).  In my *.tab file I included a conventional worksheet ("Detail_Conventional xtab") to illustrate how I'd like the "Detail1" crosstab to function (in terms of post-map-radius-selection filtering).  The "Detail_Conventional xtab" filters down like any worksheet.

                     

                    Q2, Endpoint I'm after:  I'd like to select a zip code & mile-radius on Dashboard1, have the map reflect what I've selected, have the crosstab reflect the mile-radius selection(I have it doing both now), and then be able to filter the crosstab down (independent of the map) the same way I can filter down the "Detail_Conventional xtab" crosstab.  I think (?) I know how to get a population total of the filtered down crosstab selection(a window_sum calc), however I haven't tried it yet.  I've been trying a number of configurations for a couple days and researching partitioning/addressing I'm not there yet.  Any ideas?

                     

                    The Map2 worksheet is just an alternate map that I may use in the dashboard.  It uses the Population category for color rather than a Distance gradient.

                    • 7. Re: Table calculations on radius filtering map
                      Kevin Andrist

                      Got it.  Dropping it for a week or so and then coming back it helped.

                       

                      Bethany Lyons presentation

                      http://www.tableausoftware.com/about/blog/2014/9/watch-three-top-tc14-sessions-33439?elq=397a03e1580043ed975ce8890e2dcbbc

                      with using INDEX() to visualize partitioning/addressing helped quite a bit too.  The feedback of seeing the impact of P&A changes makes things easier.

                       

                      The workbook attached uses zipcodes, us population categories, and regions.  In and of itself not terribly interesting, but this data & structure serves as a proxy for my real need of working up customer and marketing related data.

                       

                      The only issue remaining is the center-selected origin zip code doesn't display with some (random?) zip code selections.  Since dot-sizing feeds off of "center" vs "other zips", the lack of a displayed(recognized) center makes the map look like a mess. Still working on this. (example: choose zip code 48043 as the origin)