Count of complaints per State by Year
I have the count of complaints per state by year in the first picture. I have another dataset which has the population of each state by year. How do I account for population and present only the ratios of count of complaints to population?
My thought was to create a calculated field to create a ratio but I'm having trouble with adding up the number of complaint counts within a certain year and then dividing by population year. How do I write the formula that only counts complaints within 2011, 2012, etc and dividing it by that population year?
Let me know if there's an easier way to do it as well, thanks for your time.
Edit 1:
I've pivoted my second dataset and now I'm trying to graph both the counts of population and complaints. The population count across the years increases but the count of complaints stay exactly the same; its the sum of all complaints for that particular state for all the years.
Also, when I graph population count with 'Date Received' from the first dataset, I get the total population count across all the years instead of that particular year, like so:
How do I properly 'blend' in the two date variables so that it works with both population count and complaint counts in both datasets?
Edits 2:
I changed the [Years] datatype in data source 2 into a date to match the date type of [Date Received] in data source 1. I also took only the 'year' parts because it would only count things on 1/1 of each year if I used [Years] in data source 2.
Now the graphs look similar except when I'm using [Years] instead of [Date Received], all the values are about several thousand off. I tried adding another relationship except this time for month again and then it only counted values for that month.
How do I account for the discrepancy and make [Years] work just like [Date Received] ?
Reshape data source 2, following these instructions.
Then you'll be able to blend the 2 data sources on State and Month and Year.
