4 Replies Latest reply on Aug 29, 2018 5:10 PM by Jeffrey Taylor

# Need help with a determining how much of a category a subcategory represents when the subcategory spans multiple categories

I am working on a project involving US Census data and "Metropolitan Statistical Areas" or MSAs.

MSAs are metro areas that typically contain more than one county. In some cases, an MSA can cross state lines, and this is where my trouble is.

I am trying to find out how much of a state resides within an MSA. My data set is a single table with state, county and county population. Where applicable, it also includes the MSA code and MSA name.

Getting population totals for states and MSAs on their own is easy enough using LOD calculations, and these work fine. The trouble is that this doesn't work properly when the MSA spans multiple states. I can break it out and view it as a table by summing county populations, but this is limiting. My goal is to visualize the MSAs on a map (I have the Census spatial files integrated for this purpose) and then be able to color the MSAs by how much a state's population they represent. I know that getting multiple spatial files involved is a different challenge, so I am just looking for help on the calculation side of things - one thing at a time!

Here is an example of the problem I am having:

MSA NameStateState PopulationMSA PopulationMSA Population % of stateCounty PopulationCounty Population % of state
Chicago-Naperville-ElginIllinois12,830,6329,461,10573.74%8,586,60966.92%
Indiana6,483,8029,461,105145.92%708,07010.92%
Wisconsin5,686,9869,461,105166.36%166,4262.93%

I hope this helps illustrate the problem I am having (and this is also on "Sheet 2" in the attachment). In Illinois, the Chicago MSA represented about 67% of the state's population, but the LOD calculations against both the state and MSA show it to be about 74%. The same problems causes values of >100% for the other two states.

I assume there is a way to do this through an LOD calculation, but I can't figure it out. I can get state population by summing county populations when a county resides in a state. I can get MSA population by summing county populations when a county resides in an MSA. I can't seem to add the state dimension to the MSA calculation. If I can get this problem solved, then I get learn how much of a state the MSA represents, which is the dominant state in the MSA in terms of relative population, etc.

I use Desktop 10.2 (required for our Server rev), and a packaged workbook is attached.

I appreciate any help you can provide!

• ###### 1. Re: Need help with a determining how much of a category a subcategory represents when the subcategory spans multiple categories

Hi Jeffrey,

No exactly sure but you mean this?

Thanks,

Shin

• ###### 2. Re: Need help with a determining how much of a category a subcategory represents when the subcategory spans multiple categories

Shin,

I can't believe it was that simple. Thank you. This is clearly a case where my being self-taught really shines through - I had no idea you could put more than one dimension in a fixed LOD calculation.

I really appreciate your quick help!

• ###### 3. Re: Need help with a determining how much of a category a subcategory represents when the subcategory spans multiple categories

Seems like that you also need to learn LOD allows nested calc. (LOD in LOD)

{fixed  [something] : min( if [abc] = {fxied : max(abc]} then [xyz])}

Thanks,

Shin

• ###### 4. Re: Need help with a determining how much of a category a subcategory represents when the subcategory spans multiple categories

Thanks, and I will review the link. I so use nested LODs, I just couldn't find a way to make them work in this case, which makes sense - it was the wrong approach :-D