Using the measures framework

Introduction

The measures framework is used to calculate quotients (i.e. a numerator divided by a denominator) and to see how these vary over time and when broken down by different groupings.

The numerators, denominators and groups are all defined using ehrQL queries, just like we would use with a dataset definition. But the way those definitions are used is a bit different.

To explain the concepts involved we'll start with a basic but complete example and walk through each of the elements.

Basic example

Suppose we want to know what proportion of the patients prescribed atorvastatin tablets in a given month were prescribed 80mg tablets, with the results broken down by sex and calculated for each of the first six months of 2022. Here's how we'd do that using the measures framework:

Code

from ehrql import INTERVAL, case, create_measures, months, when
from ehrql.tables.core import medications, patients

# Every measure definitions file must include this line
measures = create_measures()

# Disable disclosure control for demonstration purposes.
# Values will neither be suppressed nor rounded.
measures.configure_disclosure_control(enabled=False)

# Small codelist for demonstration purposes; the real list would be longer
atorvastatin_tablets = [
    "39733211000001101",
    "39695411000001103",
    "39733011000001106",
    "39733111000001107",
]

atorvastatin_80mg_tablets = [
    "39733211000001101",
]

# The use of the special INTERVAL placeholder below is the key part of
# any measure definition as it allows the definition to be evaluated
# over a range of different intervals, rather than a fixed pair of dates
rx_in_interval = medications.where(
    medications.date.is_during(INTERVAL)
)
atorvastatin_rx = rx_in_interval.where(
    medications.dmd_code.is_in(atorvastatin_tablets)
)
atorvastatin_80_rx = rx_in_interval.where(
    medications.dmd_code.is_in(atorvastatin_80mg_tablets)
)

has_recorded_sex = patients.sex.is_in(["male", "female"])

measures.define_measure(
    name="atorva_80",
    numerator=atorvastatin_80_rx.exists_for_patient(),
    denominator=atorvastatin_rx.exists_for_patient() & has_recorded_sex,
    group_by={
        "sex": patients.sex
    },
    intervals=months(6).starting_on("2022-01-01"),
)

Running the example

You can save this file as measure_definition.py and then run the generate-measures command on it:

opensafely exec ehrql:v1 generate-measures measure_definition.py --output measures.csv

Results

This should produce a file called measures.csv whose contents look something like this:

Here the measure column always has the same value (atorva_80) because we only have a single measure defined, but if we had defined multiple measures then this would tell us which measure each row relates to.

The interval_start and interval_end columns show the date range (inclusive) covered by each row. In this case there are 12 rows because each of the six months appears twice: once where sex=male and again where sex=female.

The denominator column gives the number of patients for each interval-sex combination which match our denominator definition. The numerator column gives the number of patients which also match the numerator definition. That is, a patient must match both the denominator and numerator definitions to be included in the numerator.

The ratio is simply numerator divided by denominator.

The sex column gives the value of the expression we supplied when defining group_by. Had we defined any other groupings (say age_band) then they would appear as additional columns here.

Core concepts

Defining a measure

A measure definition always starts by creating a measures collection object:

measures = create_measures()

Each individual measure is then defined by calling measures.define_measure():

measures.define_measure(
    name="atorva_80",
    numerator=atorvastatin_80_rx.exists_for_patient(),
    denominator=atorvastatin_rx.exists_for_patient() & has_recorded_sex,
    group_by={
        "sex": patients.sex
    },
    intervals=months(6).starting_on("2022-01-01"),
)

The name argument is just used so we can identify our measure in
the output. It can be anything you like so long as it contains only
alphanumeric and underscore characters and starts with a letter.

The denominator argument defines the condition that patients must
match to be included in the denominator i.e. it is a boolean patient
series. (It is also possible
to supply an integer here, but we'll cover this later.)

The numerator arguments defines the additional condition that
patients must also meet to be included in the numerator. (Again, this
can also be an integer as we'll discuss later.)

The group_by argument defines how we would like our results broken
down. It's optional – leaving it out means that we'll get a single row
for each time interval – but most measures define at least one set of
groups. It is supplied as a dictionary mapping group names to group
definitions.

As we saw in the example above, each group name defined here ends up as
column in the results. Each group definition should be a "categorical"
patient series, that is: a patient series which takes only a fixed set
of values.

The interval argument defines the time periods over which the
measure will be calculated. This is given as a list of start date/end
date pairs but, as typing these all out by hand would be laborious, we
provide several convenience functions for generating such lists:
years,
months and
weeks.

The INTERVAL placeholder

The key difference between the ehrQL we'd write for a dataset definition and the ehrQL we write for a measure comes in this line here:

rx_in_interval = medications.where(
    medications.date.is_during(INTERVAL)
)

This filters the medications table to include just those prescribed during "the current interval" without specifying exactly what the interval is.

In a dataset definition we would need to reference a specific pair of dates here e.g.:

rx_in_interval = medications.where(
    medications.date.is_on_or_between("2022-01-01", "2022-01-31")
)

But the query we use in our measure definition needs to be evaluated over a range of date intervals, not just one. We can handle this by using the special INTERVAL value as a placeholder for the start and end dates of the intervals:

rx_in_interval = medications.where(
    medications.date.is_on_or_between(INTERVAL.start_date, INTERVAL.end_date)
)

However, it's a bit cumbersome to have to type is_on_or_between(), start_date and end_date every time so we provide is_during() as a convenient shorthand:

rx_in_interval = medications.where(
    medications.date.is_during(INTERVAL)
)

Disclosure control

By default, numerators and denominators are subject to disclosure control.
First, values less than or equal to seven are replaced with zero (suppressed);
then, values are rounded to the nearest five.

We disabled disclosure control with this line here:

measures.configure_disclosure_control(enabled=False)

However, we should carefully consider whether we wish to disable disclosure control when
running inside of the secure environment. If we don't, then we should remove that line.

Additional notes

Multiple measures

We're not restricted to just one measure per file. If we wanted to add
another measure using the same numerator and denominator but this time
broken down by age band, then we would start by defining an age band variable
like this:

age = patients.age_on(INTERVAL.start_date)
age_band = case(
    when((age >= 0) & (age < 20)).then("0-19"),
    when((age >= 20) & (age < 40)).then("20-39"),
    when((age >= 40) & (age < 60)).then("40-59"),
    when((age >= 60) & (age < 80)).then("60-79"),
    when(age >= 80).then("80+"),
)

And then we can define a new measure exactly like the previous one but
with different name and using age_band in our group_by argument:

measures.define_measure(
    name="atorva_80_by_age",
    numerator=atorvastatin_80_rx.exists_for_patient(),
    denominator=atorvastatin_rx.exists_for_patient() & has_recorded_sex,
    group_by={
        "age_band": age_band
    },
    intervals=months(6).starting_on("2022-01-01"),
)

Removing duplication

As our second measure shares so much in common with our first it seems
redundant to have to specify the numerator, denominator and intervals
all over again. Furthermore, if we ever wanted to make changes to these
definitions we'd need to make the change in multiple places to keep them
consistent.

To avoid this we can use
measures.define_defaults()
to set values which we know are going to be common between all the
measures in our file:

measures.define_defaults(
    numerator=atorvastatin_80_rx.exists_for_patient(),
    denominator=atorvastatin_rx.exists_for_patient() & has_recorded_sex,
    intervals=months(6).starting_on("2022-01-01"),
)

And then we can define our two measures using:

measures.define_measure(
    name="atorva_80",
    group_by={
        "sex": patients.sex
    },
)

measures.define_measure(
    name="atorva_80_by_age",
    group_by={
        "age_band": age_band
    },
)

Grouping by multiple features

The above example defines two separate measures, one grouping by sex and
the other by age band. But it is also possible to define a single
measure that groups by sex and age band simultaneously:

measures.define_measure(
    name="atorva_80_by_age_and_sex",
    group_by={
        "sex": patients.sex,
        "age_band": age_band,
    },
)

This will produce a row of output for each possible combination of sex
and age band: male,0-19 female,0-19, male,20-39, female,20-39
and so on for all ten combinations.

As we defined our measure to cover monthly intervals over a six month
period, this means that this single measure will produce 60 rows – ten
for each month.

Dummy data

When run outside of the secure environment ehrQL will generate dummy
data for measures just as it does with datasets. However, note that
generating meaningful results for measures defined over a large time
period and with many different groupings can require significantly more
dummy data than would be needed for a dataset definition. The measures
framework will make a crude attempt to guess how many dummy patients it
should generate, but you may need to adjust this number using the
measures.configure_dummy_data()
method.

For more information about using dummy data with measures please see our
how-to guide.