Looker Blog : Data Matters

Name Game: Step 3 - Filtered Measures

Lloyd Tabb, Founder & CTO

Jan 15, 2016

Step 3: Filtered Measures

Measures are summary computations on sets of grouped data. Typically measures are calculations such as a count of rows or a sum, average, min, or max. The grouping is based on the selected dimensions. Filtered measures are measures calculated against a specified subset of the data.

Note: In SQL you will often see this pattern written as follows. LookML makes these calculations much easier.

SUM(CASE WHEN <somecondition> THEN <number> ELSE NULL END)

In this step we are going to:

Find Old Names and New Names

We're going to find names that have not been in wide use since 1940 as well as names that have only been used since 1940.

Find Names that are Particular to the Northeast

Some names aren't in use everywhere. So, what names show up only in the Northeast? How about names that never show up in that region?

Find Names that are Gender Neutral

Most names are usually given to males or usually given to females. However, there are some names that are frequently given to both genders, such as Kerry. Does the percentage of each gender for a name vary by region?

The Code

This model inherits all the behavior of the model we used in Step 2 and adds 3 pairs of measures. Each pair contains:

  • A sum that is filtered by some dimension (related to gender, region, or year)
  • A percentage computed by the filtered sum divided by the total sum.
# Finding 
#  gender neutral vs. gender specific names
#  old names vs new names
#  northeast names vs not northeast names

- view: names_step_3
  extends: names_step_2
  fields:

  # filtered measures

  - measure: male_population
    type: sum
    sql: ${population}
    filters:
      gender: M

  - measure: male_percentage
    type: number
    sql: ${male_population} / ${total_population} 
    value_format_name: percent_2

  - measure: north_east_population
    type: sum
    sql: ${population}
    filters:
      region: Northeast

  - measure: north_east_percentage
    type: number
    sql: ${north_east_population} / ${total_population}
    value_format_name: percent_2

  - measure: before_1940_population
    type: sum
    sql: ${population}
    filters:
      year: "< 1940"

  - measure: before_1940_percentage
    type: number
    sql: ${before_1940_population} / ${total_population}
    value_format_name: percent_2

Explore!

All of the queries below can be created by Exploring From Here. Select dimensions and measures, add filters and bonk the 'Run Button'.

Names by Percent Before 1940

We simply group by the name and then order by the before 1940 percentage measure. Notice that there are a bunch of uncommon names where 100% of the occurrences were before 1940.

Explore From Here

Names by Percent Before 1940 - Filtered

We eliminate the rare names by filtering the results to show names only when the name was used at least 2000 times.

Explore From Here

Names by Percent Before 1940 - Reversed

Simply flipping the sort order shows us names that are popular (more than 2000 of total population) and were first used after 1940. Did you have any idea that Brett was a new name?

Explore From Here

Names by Percent Northeast

Using this same logic, we can find names that are particular to the northeast. These names are sorted by North East Percentage to show the names which mostly occur in the northeast.

Explore From Here

Names where Percent Male is Between 30 and 70

To look at gender-neutral names, we need to use a slightly different technique. Instead of simply sorting by the percentage measure, we want to find names where there's about the same percentage of males and females. We're going to grab the middle values by filtering the data to show only names where the percentage male is between 30 and 70 percent. Notice that Kerry is exactly gender neutral at 50 percent.

Explore From Here

Kerry by State

The use of the name Kerry varies state to state. In the northeast, it is likely to be a woman's name. In Texas, there is a 70 percent chance that the name is a man's name.

Explore From Here

Kerry by Region

We can use the region dimension we created in Step 2 to see the difference between regions. Kerry is generally a man's name in the south, a woman's name in the northeast and gender neutral in the west.

Explore From Here

Here's a list of all of the steps:

Go to the next Name Game page, Step 4: Normalizing Population with Cohorts

Next Previous

Subscribe for the Latest Posts