Name Game: 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 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]
  # filtered measures

  measure: male_population {
    type: sum
    sql: ${population} ;;

    filters: {
      field: gender
      value: "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: {
      field: region
      value: "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: {
      field: year
      value: "< 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 . Select dimensions and measures, add filters and bonk the 'Run Button'.

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.

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

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?

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.

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.

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.

We can use the region dimension we created in 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.

Here's a list of all of the steps: