Summarise temporal trends in OMOP tables

In OmopSketch: Characterise Tables of an OMOP Common Data Model Instance

Introduction

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

In this vignette, we will explore the OmopSketch function summariseTrend(), which summarises temporal trends from OMOP CDM tables. This function allows you to visualise how key measures (such as number of records, number of persons, person-days, age, or sex distribution) change over time.

Create a mock cdm

Let's start by loading essential packages and creating a mock CDM using the R package omock.

library(omock)
library(OmopSketch)
library(dplyr)
library(visOmopResults)

cdm <- mockCdmFromDataset(datasetName = "GiBleed", source = "duckdb")

cdm

Summarise temporal trends

Let's use summariseTrend() to get an overview of table content over time. In this example, we summarise yearly trends for the event tables condition_occurrence and drug_exposure, and include observation_period as an episode table.

summarisedResult <- summariseTrend(
  cdm = cdm,
  event = c("condition_occurrence", "drug_exposure"),
  episode = "observation_period",
  interval = "years"
)

summarisedResult |>
  glimpse()

Notice that the output is in the summarised result format.

What are event and episode tables?

• Event tables capture records that are assigned to a single point in time for trend summaries (for example, a diagnosis, drug exposure start, or measurement). Event records are included when their start date falls within the study period, and each record contributes to the interval containing its start date.

• Episode tables describe periods that span time (for example, observation periods or eras). Episode records are included when their start or end date overlaps the study period. They are trimmed to the study period and can contribute to every interval they overlap.

You can check whether a table was treated as an event or an episode table in the settings of the summarised result:

summarisedResult |>
  addSettings(settingsColumn = "type") |>
  glimpse()

Outputs

You can choose what to summarise using the output argument. Options include:

• "record": number of records (default value).

• "person": number of distinct subjects.

• "person-days": number of person-days (episode tables only).

• "age": median age.

• "sex": number of females.

Records

For each time interval, the results include the number of records during that period. In addition to absolute counts, the function reports the percentage of records in each interval relative to the total number of records in the table after any date restriction.

summarisedResult <- summariseTrend(
  cdm = cdm,
  event = "condition_occurrence",
  output = "record",
  interval = "years"
)

summarisedResult |>
  select(group_level, variable_name, additional_level, estimate_name, estimate_value)

For episode tables with output = "record" and a time interval such as "years", record counts are split into two measures: Number of records: start_date counts episodes that start in each interval, and Number of records: end_date counts episodes that end in each interval. The overall result still uses Number of records for the total number of episode records.

For example, using observation_period as an episode table:

summarisedResult <- summariseTrend(
  cdm = cdm,
  episode = "observation_period",
  output = "record",
  interval = "years"
)

summarisedResult |>
  filter(grepl("Number of records", variable_name)) |>
  select(variable_name, additional_level, estimate_name, estimate_value)

Subjects

For each time interval, output = "person" returns the number of distinct subjects with records during that period. The percentage estimate uses the number of subjects in the person table as the denominator.

summarisedResult <- summariseTrend(
  cdm = cdm,
  event = "condition_occurrence",
  output = "person",
  interval = "years"
)

summarisedResult |>
  select(group_level, variable_name, additional_level, estimate_name, estimate_value)

Person-days

When an episode table is specified, you can include "person-days" in the output to summarise total follow-up time across intervals. The results include both the number of person-days in each interval and the percentage relative to the total person-days in the episode table after any date restriction.

summarisedResult <- summariseTrend(
  cdm = cdm,
  episode = "observation_period",
  output = "person-days",
  interval = "years"
)

summarisedResult |>
  select(group_level, variable_name, additional_level, estimate_name, estimate_value)

The function automatically skips "person-days" for event tables.

summarisedResult <- summariseTrend(
  cdm = cdm,
  event = "visit_occurrence",
  output = "person-days",
  interval = "years"
)
summarisedResult

Age

When "age" is included in the output argument, the function reports the median age of subjects. For event tables, age is calculated at the record start date. For episode tables summarised by interval, age is calculated at the later of the episode start date and the interval start date.

summarisedResult <- summariseTrend(
  cdm = cdm,
  event = "condition_occurrence",
  output = "age",
  interval = "years"
)

summarisedResult |>
  select(variable_name, additional_level, estimate_name, estimate_value)

Sex

When "sex" is included in the output argument, the function counts the number of female subjects in each time interval. It also provides the percentage of females relative to subjects with a recorded male or female sex in the table after any date restriction.

summarisedResult <- summariseTrend(
  cdm = cdm,
  event = "condition_occurrence",
  output = "sex",
  interval = "years"
)
summarisedResult |>
  select(variable_name, additional_level, estimate_name, estimate_value)

Intervals

The interval argument controls the temporal granularity of the results. Possible values are "overall" (default, no stratification by time), "years", "quarters", and "months".

For example, to see quarterly trends:

summarisedResult <- summariseTrend(
  cdm = cdm,
  event = "condition_occurrence",
  interval = "quarters",
  output = "record"
)

summarisedResult |>
  select(additional_level, estimate_value)

Stratify by age and sex

You can use the ageGroup and sex arguments to stratify the results. Age groups are assigned using the record start date for event tables and the relevant episode start date or interval start date for episode tables.

summarisedResult <- summariseTrend(
  cdm = cdm,
  event = "condition_occurrence",
  interval = "years",
  output = c("record", "age", "sex"),
  ageGroup = list("<35" = c(0, 34), ">=35" = c(35, Inf)),
  sex = TRUE
)

summarisedResult |>
  select(variable_name, strata_level, estimate_name, estimate_value)

By default, the output includes the "overall" group as well as combined strata (for example, Female and >=35). For output = "sex", sex stratification is not applied because a single estimate summarising the female population is returned.

In-observation stratification

When inObservation = TRUE, the results are stratified by whether each record occurred within the subject's observation period. For episode records, both the start and end date must fall within an observation period to be labelled as in observation. This can be useful for identifying data quality issues or assessing completeness.

summarisedResult <- summariseTrend(
  cdm = cdm,
  event = "condition_occurrence",
  interval = "overall",
  output = "record",
  inObservation = TRUE
)

summarisedResult |>
  select(variable_name, strata_name, strata_level, estimate_name, estimate_value)

Date Range

You can restrict the study period using the dateRange argument. Event records are kept when their start date is within the date range. Episode records are kept when they overlap the date range and are trimmed to the requested period.

summarisedResult <- summariseTrend(
  cdm = cdm,
  event = "drug_exposure",
  dateRange = as.Date(c("1990-01-01", "2010-01-01"))
)

summarisedResult |>
  settings() |>
  glimpse()

Tidy the summarised object with tableTrend

tableTrend() helps you convert a summarised result into a formatted table for reporting or inspection. The table type can be set with the type argument; supported formats are provided by visOmopResults::tableType(). If type = NULL, global table options are used when available; otherwise, a gt table is created by default.

result <- summariseTrend(
  cdm = cdm,
  event = "condition_occurrence",
  episode = "observation_period",
  output = "age",
  interval = "years"
)
tableTrend(result = result)

Visualise trends with plotTrend

plotTrend() builds a ggplot2 visualisation from a summariseTrend() result.

result <- summariseTrend(
  cdm = cdm,
  event = "measurement", 
  interval = "quarters",
  sex = TRUE, 
  ageGroup = list(c(0, 17), c(18, Inf)),
  dateRange = as.Date(c("2010-01-01", "2019-12-31"))
)

plotTrend(
  result = result,
  colour = "sex",
  facet = "age_group"
)

When the result includes several outputs (for example, records, subjects, or person-days), select the measure to visualise with the output argument.

result <- summariseTrend(cdm,
  event = "measurement",
  interval = "quarters",
  output = c("sex", "record"),
  dateRange = as.Date(c("2010-01-01", "2019-12-31"))
)
plotTrend(
  result = result,
  output = "sex"
)

You can also specify faceting with a formula or column name, and colour using one of the tidied result columns.

result <- summariseTrend(cdm,
  event = "measurement",
  interval = "quarters",
  sex = TRUE,
  inObservation = TRUE,
  dateRange = as.Date(c("2010-01-01", "2019-12-31"))
)
plotTrend(
  result = result,
  facet = omop_table ~ sex,
  colour = "in_observation"
)

Disconnect from CDM

Finally, disconnect from the mock CDM.

cdmDisconnect(cdm = cdm)

OmopSketch documentation built on Aug. 27, 2026, 5:07 p.m.