7 Replies Latest reply on Dec 4, 2013 12:42 PM by Jeff Thurm

    Need Help Building a Dynamic Time-Series Map using Colors/Shading

    Jeff Thurm

      I'm pretty new to Tableau and I was hoping I could get some help on how to build out the following visualization that I'm having a bit of trouble with it.


      I have a csv file where in Column A, I have the reference #'s of various businesses, in Column B, I have the Year, in Column C, I have the ZipCode of each business, and in Column D, I have data about a certain variable (let's say "$ Funding Level") for each respective business pertaining to that particular year.


      I've attached a sample csv file.

       

      I want to build a dynamic "movie" map visualization that will show me each respective business at its location on the map (using the ZipCode provided) along with its respective "Funding Level" colored to represent magnitude... (for example, a high "$ Funding Level" value = dark blue vs. a low "$ Funding Level" value = light blue), and then "play" the visualization so we can see the distribution of the light vs. dark blue over the years (given in Column B)


      I've been playing with it but am having a lot of trouble… if anyone is up for the challenge, I’d really appreciate it!

        • 1. Re: Need Help Building a Dynamic Time-Series Map using Colors/Shading
          Ramon Martinez

          Hi Jeff,

           

          I was playing with your data set and I created a workbook for you.

           

          I want to start saying that there are 10,235 out of 23,743 records with missing (Null) values in the field ZIP. This means that there are a significant number of businesses with ZIP unknown (having null value). See worksheet Missing values in ZIP in the attached workbook

          This will impact the presentation of the data in maps by Zip Code.

           

          In your data, all records from year 1996 to 2003 have null values in the field ZIP. See worksheet No., of Business by ZipCode and Year in the attached workbook.

           

          The missing values in the field ZIP need to be taken into account when analyzing the data and presenting the results.

           

          There is other thing that I want to call your attention: a Zip code includes multiple businesses, remember that zip code is an area. As it is illustrated in the worksheet No., of Business by ZipCode and Year

           

          You wrote:

          I want to build a dynamic "movie" map visualization that will show me each respective business at its location on the map (using the ZipCode provided) along with its respective "Funding Level" colored to represent magnitude... (for example, a high "$ Funding Level" value = dark blue vs. a low "$ Funding Level" value = light blue), and then "play" the visualization so we can see the distribution of the light vs. dark blue over the years (given in Column B)

           

          As a zip code includes many businesses, it is not possible to show in a map the location of every specific business, coding the field Funding (for that specific business) per year.  Instead, we can present data aggregated by ZIP code as it is illustrated in the attached workbook.

           

          I added a a couple of map worksheets, a table view and a dashboard to illustrate your data. Using the dashboard you are able to see the distribution of number of businesses per ZIP per an specific year, that can be selected with the page control located at the right-top side of the dashboard.

          Hovering the mouse over a zip area you get additional information: number of businesses and total funding in that zip.

          Clicking on a zip code will show in the list of businesses -located at the right side of the dashboard-  in the selected zip code.

           

          I hope this helps you to move forward your project.

           

          Best,

          Ramon

          • 2. Re: Need Help Building a Dynamic Time-Series Map using Colors/Shading
            muthu.krishnan

            Hi Jeff,

             

            In the data window, double click the zipcode. you ll get everything from the data file.

             

            Regards,

            Muthu Krishnan. M

            • 3. Re: Need Help Building a Dynamic Time-Series Map using Colors/Shading
              Jeff Thurm

              Thanks Ramon - this helped a lot... just a few follow up questions...

               

              1) Your idea about aggregating the data by zipcode makes sense - As such, the "filled map" is the visualization I was looking for. How do I show the AVERAGE funding level per zipcode for all rows that have a zipcode listed ONLY?

               

              2) This AVG value is what I want to display using the shading concept over time. How do I edit the color scale of the shading... i.e. from light blue to dark blue or from blue to red etc...

               

              3) Lets say I want to swap out the data on Funding level to other data I have on these businesses (lets say Earnings)... is there a simple way to swap out the variable but still show the same visualization (with the only exception being maybe use the SUM of the earnings per zipcode over time, rather than the AVG)?

               

              4) Can you walk me through the steps you did to build the "Filled Map Time Series" tab visualization, so I can build it independently... this way I'm comfortable going forward on similar visualizations.

              • 4. Re: Re: Need Help Building a Dynamic Time-Series Map using Colors/Shading
                Ramon Martinez

                Hi Jeff,

                 

                I will go through your points:

                 

                1.- Add the measure Funding to Color, then replace the aggregation function from SUM([Funding]) to AVG([Funding]). See the worksheet Filled Map Avg of Funding per Zip in the attached workbook.

                 

                2.- Hover the mouse over the top-right corner of the color legend, a symbol will be shown, click on it and select Edit Colors... a dialogue will allow you to change the color schema.

                 

                3.- You can change the measure to be display in the filled map just by adding the specific measure to Color, then define the aggregating function (SUM, AVG, etc.)

                 

                4.- The easy way to build the filled map is to select the dimension ZIP, holding Ctrl key click on measure Funding, then click on Show Me (located at the right-top corner of Tableau) and select Filled Map visual.

                 

                I edited the workbook to show average of Funding per ZIP, see worksheet Filled Map Avg of Funding per Zip and the Dashboard 1 in the attached workbook.

                 

                It is pleasure to help you.

                 

                Ramon

                • 5. Re: Need Help Building a Dynamic Time-Series Map using Colors/Shading
                  Jeff Thurm

                  Thanks Ramon,

                   

                  And how were you able to aggregate the data by year to turn it into a "movie" that can then be played to show the shading change over time?

                  • 6. Re: Need Help Building a Dynamic Time-Series Map using Colors/Shading
                    Jeff Thurm

                    Also, is there a way for me to add labels to this map, to show that this particular area is Manhattan vs. this area is Queens, this area is Brooklyn etc...?

                    • 7. Re: Re: Re: Need Help Building a Dynamic Time-Series Map using Colors/Shading
                      Jeff Thurm

                      Hi Ramon,

                       

                      Awhile ago you helped me build the attached twbx file (a time-series map using colors/shading that can be "played like a movie" to show the magnitude of the data at each point in time)... I really appreciate the help.

                       

                      I've been trying to apply it to the csv file attached (Enrollment)... i.e. using a new/different data connection

                       

                      Basically, what I'm trying to do is make the EXACT SAME visualization but substitute column R in the csv file ("AVG") in place of "Funding" in the twbx file. The other things I'll need in order to match the twbx visualization exactly are the zip code (found in column B) and year (found in column A), so all the info. needed is there... I just can't get it to work.

                       

                      If you can help, would you be able to send me a new twbx file that does this and, if you can, walk me through the steps so I'll be able to repeat the process going forward.

                       

                      Also, is there a way to be able to change the values in column R in the "enrollment" csv file and have the changes automatically take effect in the tableau visualization thats built using the data from that column/source? If so, can you also walk me through those steps so I'll be able to repeat the process going forward.

                       

                      Best,

                       

                      Jeff