2 Replies Latest reply on Apr 9, 2018 8:29 AM by Amanda Lamb

    Help with LOD calculations

    Amanda Lamb

      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!

        • 1. Re: Help with LOD calculations
          Mike Mainzer

          So one problem I noticed in your sample data is that your population table has gender included in the same field as all of the races. So you need to make the population measure an average or an attribute instead of a sum.

           

          Bring in all three of your tables separately. Then, select Data>>Edit Relationships. Each of your tables are in the dropdown. Select each of them and choose the fields that they relate to the other two tables on. This way, Tableau will have an idea of how to relate your tables when you build relationships.

           

          As far as the year column though, I just converted to string.

           

          Screen Shot 2018-04-06 at 8.32.32 PM.png

           

          1. Bring all three tables in separately.

          2. Edit relationships per instructions above

          3. In your 'Services' table create two calc fields that are simply the totals from your other two tables - one of population and one for eligibles

          4. Make sure that the population calc field is an average, not a sum (or an attr)

          5. Build the view above

          6. Make sure your other two tables are linking on the appropriate fields, like so:

           

          Screen Shot 2018-04-06 at 8.38.02 PM.png  Screen Shot 2018-04-06 at 8.37.55 PM.png

           

          Not sure if this is what you're looking for, but hopefully it helps.

          1 of 1 people found this helpful
          • 2. Re: Help with LOD calculations
            Amanda Lamb

            This worked great, thank you! Way less complicated than everything I was trying to do.