5 Replies Latest reply on Sep 4, 2017 11:40 AM by Larry Staley

    My US Map in Tableu NOT displaying correctly

    Larry Staley
    Larry Staley Sep 2, 2017 12:21 AM

      I am relatively new to Tableau and attempting to create a US Map showing three highest states (each year) and three lowest states (same year) by income in top 10% and in Bottom 90%.  I made data for years 2010 - 2012 and shown in attached xls file.  I was able to make the filter and have a filled in state map.  However, when I filtered for 2012 ONLY Vermont showed (corresponding to column called State6) but the other five states for New York, Colorado, California, Delaware, and North Dakota (representing State1 through State5 columns) are NOT being displayed on the map.

       

      Need some help here so I can get all six displayed and for whatever year I filter.

      I attached in addition to the XLS file the workbook for this map I described, called map.twb.

      Thanks in advance.

       

      L. Staley

      September 1, 2017

      • 199 Views
      • Categories: Maps & Geocoding
      • Tags:
        • 1. Re: My US Map in Tableu NOT displaying correctly
          Jim Dehner
          Jim Dehner Ambassador Sep 2, 2017 5:05 AM (in response to Larry Staley)

          Hi Larry

           

          See below

          The reason you are having problems is that all your dimensions and measures are unique - they don't relate to one another -

          Think of an excel sheet (a cell driven application) that has a separate column for each measure and each dimension - and some one asks what are the top and bottom 3 states - the answer is look in columns B through G

           

          Tableau is a data base driven application where you are working with a an entire dimension at a time - you are trying to plot 6 dimensions at once but they are mutually exclusive

          To correct that you can pivot the state 1 - state 6 columns around the rest of the data

               open the data source

               select the first state column and shift select the last column to highlight all six

               right click a header and select pivot

           

          you will have 2 columns - I changed the measure to "State Rank" (it will be your former header) and the Dimension to "6 State"

          6-State will show up as string field - convert it to a geo filed by right clicking and select geographic property then select state/province

           

          Now drag it to the canvas and it will create the view below

           

           

          Now all that said - you still have issues with the measures - again - each is an individual measure that relates to you state 1-6 original ranking -

          the data structure would be better suited

          and so on -

           

          I know that is not what you wanted to hear - but you may be able to do some of the restructuring pivoting in excel or with a custom SQL

          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.

          1 of 1 people found this helpful
          • 2. Re: My US Map in Tableu NOT displaying correctly
            Larry Staley
            Larry Staley Sep 2, 2017 4:23 PM (in response to Jim Dehner)

            Hi Jim,

             

            Thanks for your prompt response.  Attempting to follow what you mentioned but still getting a little confused.  Have attached to screen shots.  Believe I got through the "right click a header and select pivot" if the attached pivot.jpg is correct?  However, what I have on the canvass is not right (see pivots_on_canvas.jpg).  I have done pivot charts before but only in Excel.  Maybe I messed up one of your steps.  You will need to be very deliberate with me.  Still learning Tableau plus maybe with heat wave here in the SF Bay Area, well harder to think correctly. 

             

            Thanks again.

             

            L. Staley

            • 3. Re: My US Map in Tableu NOT displaying correctly
              Larry Staley
              Larry Staley Sep 2, 2017 4:23 PM (in response to Larry Staley)

              Hi Jim,

               

              Think I at least got to emulate your first geo map showing the six states (to 3 at 10% income level and to 3 at bottom 90% level), filtered for year 2012.  The twbx file is attached.

               

              However, still not clear on the values (and really on the map only want to show the value with each state, for example, if Click on New York would see $720,000, Colorado, $705,000 etc.  So not clear what you mean on the data view for values.  To do this do I repeat for the values (as state) and add it to the pivot table?

              Thanks again.

              • 4. Re: My US Map in Tableu NOT displaying correctly
                Jim Dehner
                Jim Dehner Ambassador Sep 3, 2017 5:34 AM (in response to Larry Staley)

                Hi Larry

                I tried to explain this in my first post - the issue is with how your data is structured - Pivoted or unpivoted your data needs to have a tie between the states that you want to plot and the values for the measure you want to plot -

                your data (unpivoted) is has a single record for 2012 - (see attached an the broken image below - in that record in separate fields you have states 1 - 6 listed and the 6 separate fields you have the value of the 10% invome level

                 

                 

                 

                 

                Your data needs to be structured like this -

                 

                 

                In what I showed you before only pivoted the the 6 states to give you an example of how the data needs to be structured - you will not be able to get the all the individual data fields pivoted into a good data structure inside Tableau

                you will need to do it either in excel or with a custom SQL before you bring it into tableau

                 

                I know it is not what you want to hear but you will need to restructure the data to get what you want

                 

                Jim

                1 of 1 people found this helpful
                • 5. Re: My US Map in Tableu NOT displaying correctly
                  Larry Staley
                  Larry Staley Sep 4, 2017 11:40 AM (in response to Jim Dehner)

                  Hi Jim,

                   

                  Happy Labor Day.  I think I am largely there now and data shows the same groups, multiple years with all six states.  I have attached a workbook.  Issue becomes with multiple year filters, in this case 2011 and 2012.  First here is actual base data again:

                   

                  Top 10% top incomes:

                  2012  New York, $720,000; Colorado $705,000; California $702,000

                  2011  New York $700,000; California $675,000; Colorado $650,000

                   

                  Bottom 90% top incomes:

                  2012 Delaware $66,000; North Dakota $64,000; Vermont  $61,500

                  2011 Florida $65,000; Ohio $62,000; Minnesota $61,500

                   

                  For top three states:

                   

                  Click on New York, shows State 1, $720,000 (based on 2012) CORRECT

                  Click on Colorado, shows State 2, $705,000 (based on 2012) CORRECT

                  Click on California, shows State 2, $675,000 (based on 2011) NOT CORRECT, as should show State 3, $702,000

                   

                  For bottom three states:

                   

                  Click on Delaware, shows State 4, $66,000 (based on 2012) CORRECT

                  Click on North Dakota, shows State 5, $64,000 (based on 2012) CORRECT

                  Click on Vermont, shows State 6, $61,500 (based on 2012) CORRECT

                  Click on Minnesota, shows State 6, $61,500 (based on 2011) CORRECT

                  However, both states Ohio and Florida are showing and SHOULD NOT be showing (Ohio would be behind North Dakota as State 5 and Florida behind Delaware as State 1).

                   

                  I don't know if an additional counter is needed?  I did use calculated fields successfully to pull the correct income amounts.  Again, the workbook is attached.

                   

                  Thanks goodness the San Francisco Bay Area is cooling off!

                   

                  Thanks again.

                  L. Staley