3 Replies Latest reply on Jan 2, 2014 9:30 AM by Matt Lutton

    Calculate year-to-year population change on map with filter

    Emily Le Coz

      I have U.S. Census data showing state populations in 2010, 2011, 2012 and 2013. I put them into a filled map and put a filter on the year. I want to be able to scroll through the years and show each state's percentage of population growth or decline relative to the previous year (or relative to 2010 - not sure which I want to use, yet).

       

      But it's not working. I suspect it has something to do with the filter, but I can't figure it out.

       

      Any ideas?

       

      Thanks!

        • 1. Re: Calculate year-to-year population change on map with filter
          Shawn Wallwork

          Try moving the year from the filter shelf to the page shelf, then you can 'scroll through years'. As to your other question you can add a Quick Table Calculation to do both values you mentioned. However population doesn't really change that much so the changes are quite subtle. The first year will all be the one color because there's no previous year to compare to.

           

          Thanks for posting the packaged workbook, but it would be helpful if you also mentioned which version you are using. I don't have 8.0 on this machine, so I couldn't post a 8.0 workbook for you.

           

          --Shawn

          • 2. Re: Calculate year-to-year population change on map with filter
            Matt Lutton

            Hi Emily:

             

            I took a stab at this and believe I've had some success after some trial runs.  Here is what I did:

             

            1) Changed your % Difference (based on previous year) calc to be:

            (ZN(SUM([Population])) - LOOKUP(ZN(SUM([Population])), -1)) / ABS(LOOKUP(ZN(SUM([Population])), -1))

             

            2) Set the Compute Using for this % Difference Calc appropriately:

            PercentDiffCalc.png

             

            3) With YEAR on the filter shelf, you could not get accurate results since only one year was available to the calculation.  So, moving YEAR(Date) to the Pages shelf, which gives us the ability to "page through" the years and see the resulting values.  Version 8.1 Workbook is attached.  Note that 2010 will show all NULL values, since there is no previous year.  We could set this up to calculate the change from 2010 for each year as well.  I set up a separate calculation for this in the attached workbook, and created a sheet for each version.

             

            I hope this helps.  If you don't have version 8.1, I can re-create in Version 8.0, but the steps would all be the same.

            • 3. Re: Calculate year-to-year population change on map with filter
              Matt Lutton


              After looking back at this closely, all you really need to do in your workbook is move YEAR(Date) to the pages shelf, and the values should be correct.  Version 8.0 workbook attached with only that step completed, and labels showing for each state (again 2010 will show no values/all one color)

               

              Note that this example is comparing all years to 2010. We could change that if needed.

              1 of 1 people found this helpful