Tuesday, May 4, 2010

Plotting against a zero value

Interesting request for a visual... The selected category should exist at 0,0 and then other categories should be compared against this category. I'll write this up in further detail as time permits.

The example below shows state "Per Capita Income" and "Percentage of College Degrees" against all other states. The selected state is positioned at 0,0.

The data comes from http://www.census.gov/compendia/statab/rankings.html

More information describing how to do this is shown below the visual.

This was basically a self join on the table, where state <> state (not equal to). Then, I was able to create very simple calcs to create the X and Y coordinates for "other states" as compared to my selected state.

Here is a picture of the join:

and here is the actual data that I used. I had to combine the two statistics (income per capita, and percent BA degrees) into one table, as the census folks don't like handing out arbitrary cohort analytics to the general public!! :)


Andy said...

That's a really good trick, "Alan". I downloaded the workbook - you only used a couple of calculated fields, so I assume you've done something at the connection level?

We'd make use of this kind of thing quite a lot, I think - look forward to when time permits you to write it up....

NorthwestCoder said...

filled out this post somewhat. The answer is self-join on 'state' not equal to 'state'.