I am having a lot of trouble with something that should be very simple. I have attached a sample dataset similar to what I am working with. I want to be able to analyze a population that is receiving services, compare it to a population of eligible people, and then to the overall city population. But I am having multiple problems. I do not have SQL so I can't change how the datasets look before putting them in Tableau. I'd have to manually fix them to get them to look any different.
First, because the service dataset includes which service (10 different kinds in most quarters), there are multiple rows for every Year, Quarter, Race, and Gender. So when I try to sum eligible and population, I get very large totals that don't reflect the true base total. One simple solution would be to just divide by 10, but the number of services can change in any year/quarter, as can the number of races or genders. I basically want a COUNTD on the number. I think that is an LOD calculation, but since there are so many different fields I need to control for, I can't wrap my head around how to write the calcs.
While we're at it, I'd also love the year field to be a date field that just represents the year and doesn't convert to month/day. When I try to convert using YEAR() everything becomes 1905 and I can't figure out why.
One thing that might be complicated is that I have to join the three different datasets on Year, Quarter (except population), Race, and Gender.
Also bear in mind the real data/Tableau project I am working with is much larger and slightly more complicated. I am just trying to understand the concepts so I can apply them. I appreciate any help or direction to other resources!