5 Replies Latest reply on Mar 27, 2020 9:04 AM by Graham Keller

    Population Completeness

    Jonathan Stoudt

      I am merging 2 datasets to create a rate based on the following:

        1. Population by county, state and nation (US). This is derived through census data.

        2. Occurrence by county. If there is no occurrence, the county is not listed.

       

      I am not able to figure out how to tag counties with no occurances to a zero value. It is causing issues with the role from the county, to the state, to the national level as portions of the "population" are missing. Said another way,  the sum of populations do not add back to the actual population in the US census data.

        • 1. Re: Population Completeness
          Budi Lubis

          Hi Jonathan -

          If you want to show all the census data, do a left instead of inner join.  It will keep all your census data intact.

           

          Untitled 6.png

          • 2. Re: Population Completeness
            Graham Keller

            Hi Jonathan,

             

            You could do a couple things here. It appears that there is almost no data missing from your "Population Estimates" file for Area_Name (count) and State, but it appears there are many "County" data points that are not reported in the NYT source.

             

            One thing you could do is to use the "State" and "Area_Name" fields instead of the "County" and "State (Case Count NYT)" fields. The only caveat is that there will be a bunch of null values in your dataset. To try to combat this, you can create new measures and wrap them all in ZN(). This will say, if there is a null value, return a zero instead. Then you can try to proceed with that.

             

            The other way that is probably better to go about this would be to blend your data, instead of trying to join the 2 sources together. Blend Your Data - Tableau

             

            Hope this helps!

            -Graham

            • 3. Re: Population Completeness
              Jonathan Stoudt

              Thanks for the advice - unfortunately I'm still having issues. I'm getting error messages saying I can't use different levels of aggregation. I can easily create another workbook and do the analysis that way, but I would like to utilize the tool to it's fullest.

               

              Do you know if it's possible to create 2 independent data connections within the same workbook? I could create a link at the county level and at the state level to arrive at the results I need. Basically create 2 tabs in each excel workbook and link the two for different analysis.

               

              • 4. Re: Population Completeness
                Jonathan Stoudt

                Thanks for the advice on ZN by the way - helpful for another task I'm trying to complete. Still learning tricks of the trade.

                • 5. Re: Population Completeness
                  Graham Keller

                  If you're using different levels of aggregation, you'd have to use data blending.

                   

                  If you tried to join two data sources, one at city level, one at state level, you'd get a bunch of duplicates in your data and it would look funky (not the good kind).

                   

                  For example, if you had data for Dallas and Houston from one source (cases) and data for Texas in another (population), your row-level data would look like this: