ESP Query Composer Projections v4.0.3
This documentation references PopMedNet 4.0.3
For version 5.0 documentation, please see PopMedNet User's Guide
Distributed Query Tool - User's Guide
Based on version 4.0.3
ESP Query Composer Projections and Adjustments
The ESP Query Composer Projections and Adjustments functionality allows you to define a cohort of patients using complex inclusion and exclusion criteria and then uses census data for a specified location to generate three sets of stratified patient counts: observed patients, adjusted patient count, and projected patient count. This functionality helps to correct for skewed patient demographics among a network's data partners and provides estimated patient counts for locations not covered by the data partners.
The ESP Query Composer request is used to define a cohort of patients. When the request is run at a DataMart, two queries are executed: one to find the cohort of interest (observed patients) and another to produce a data set of the data partner's overall patient population demographics. These two data sets are then compared to census data for a specified location to generate adjusted and projected patient counts. See the table below for more information on these data sets.
| Data set | Description | Stratification |
|---|---|---|
| Observed Patients | Count of patients in the defined cohort of interest. | May be stratified by 10 year age group, sex, and race-ethnicity. |
| Observed Population | Count of patients from the location(s) of interest with at least one visit of any kind in the two years prior to the end of the observation period of interest. If no observation period end date is defined, then this data set includes patients with at least one visit of any kind in the two years prior to the date that the request is executed by the data partner. | Stratified according to the stratification options selected for the cohort of interest, which may include 10 year age group, sex, and race-ethnicity. |
| Census Population | Count of people from the location of interested from the 2010 census. | Stratified by 10 year age group, sex, and race-ethnicity. |
Composing an ESP Query Composer Projections Request
The projections and adjustments functionality uses the same Query Composer request type to define a cohort of interest. See Query Composer Request for more information on how to define a cohort, however note that in order to generate interpret-able adjusted and projected patient counts, there are some restrictions on possible request parameters. These restrictions and instructions are detailed below.
If the restrictions and instructions detailed below are not followed, then you may not be able to view adjusted and project patients counts or the counts may not be easily interpreted.
Terms
There are no restrictions on the terms that you may use in a criteria group.
Criteria Groups
There are no restrictions on the number of criteria groups that you may use to define your cohort. However, if your request does not define an observation period or defines an observation period that is larger than two years, then an additional criteria group must be added to ensure that the denominator for your observed patients matches the denominator of the observed population.
If this criteria group is not added, then there will be a mismatch between the denominators which will affect the interpretability of your results and could produce an outcome where the observed patient count is larger than the observed population count for a strata. See the diagram to to the right for a visual explanation of why this is necessary.
This additional criteria group should be defined as:
- Minimum 1 visit
- Observation Period
- Start Date: Two years prior to your end date
- End Date: The end date for the observation period of your primary criteria group or, if no end date was defined for the primary criteria group, today's date.
In the example to the right, the observation period in the primary criteria group is 01/01/2008 - 01/01/2012. Because this observation period is larger than two years, the additional criteria group is necessary. It is defined as minimum one visit, 01/01/2010 - 01/01/2012.
Result Stratification
Any combination of the following stratification options are permitted. You may also choose not to stratify the results, in which case a single count will be returned.
- Age: 10 Year Age Group
- Sex
- Race-Ethnicity (see below for how race-ethnicity is defined)
If any stratification options other than these are selected, you will not be able to view the adjusted and projected counts of the results.
Viewing Results
View the results of a request from the Request Detail page by selecting the DataMarts to view in the Received Responses panel and clicking “View Results.” Click "Projected View" to view the adjusted and projected patient counts.
The results table will be displayed in the Response panel of the results page. Use the dropdown menus at the top right to switch between states, regions, and towns of interest. When a new location is selected, the values in the Census Population, Census Population %, Adjusted Observed Patients, and Projected Patients columns will populate with new values based off of the census data for the location.
In addition to the stratification options selected (Ten Year Age Group, Sex, and/or Race-Ethnicity), the following columns are displayed in the results table:
| Patient Counts | Description | Calculation |
|---|---|---|
| Observed Patients | Raw count of patients with selected outcomes in the source DataMarts | None |
| Observed Population | Count of patients from the location(s) of interest with at least one visit of any kind in the two years prior to the end of the observation period of interest. If no observation period end date is defined, then this data set includes patients with at least one visit of any kind in the two years prior to the date that the request is executed by the data partner. | None |
| Observed Population % | Percentage of the population from the source DataMarts in the strata | (Observed population strata / Σ(Observed population strata))*100 |
| Census Population | Count of people from the specified location of interested from the 2010 census. | N/A |
| Census Population % | Percentage of the census population in the strata | (Census population strata / Σ(Census population strata ))*100 |
| Adjusted Patient Count | Adjusted count of patients with selected outcomes in the source DataMarts after taking into account differences in the age, sex, and/or race-ethnicity distribution in the DataMart population compared to the census population in the region of interest | Observed patient strata * (census population % strata / observed population % strata) |
| Projected Patient Count | Projected number of patients with the outcome in the specified region of interest, after taking into account the census population demographics for the region of the interest | Observed patient strata * (census population strata / observed population strata) |
Additional Information
Race-Ethnicity Definitions
In order to produce data sets that could be compared to census data, the following mappings were created to map ESP Race and Hispanic values to ESP: MDPHnet Race-Ethnicity values. Additionally, when viewing projected results, ESP: MDPHnet Race-Ethnicity values are mapped to census values. These mappings are described below. The ESP: MDPHnet Race-Ethnicity mappings are displayed when viewing results.
ESP Race and Hispanic Mappings to ESP: MDPHnet Race-Ethnicity
| ESP Race Values | ESP Hispanic Values | ESP: MDPHnet Race-Ethnicity |
|---|---|---|
| White | Yes | Hispanic |
| White | No | White |
| White | Missing / null | White |
| White | Unknown | White |
| Black or African American | Yes | Hispanic |
| Black or African American | No | Black |
| Black or African American | Missing / null | Black |
| Black or African American | Unknown | Black |
| Asian | Yes | Hispanic |
| Asian | No | Asian |
| Asian | Missing / null | Asian |
| Asian | Unknown | Asian |
| American Indian or Alaska Native | Yes | Hispanic |
| American Indian or Alaska Native | No | American Indian or Alaska Native |
| American Indian or Alaska Native | Missing / null | American Indian or Alaska Native |
| American Indian or Alaska Native | Unknown | American Indian or Alaska Native |
| Native Hawaiian or Other Pacific Islander | Yes | Hispanic |
| Native Hawaiian or Other Pacific Islander | No | Asian |
| Native Hawaiian or Other Pacific Islander | Missing / null | Asian |
| Native Hawaiian or Other Pacific Islander | Unknown | Asian |
| Unknown | Yes | Hispanic |
| Unknown | No | Unknown |
| Unknown | Missing / null | Unknown |
| Unknown | Unknown | Unknown |
ESP: MDPHnet Mappings to Census Values
| ESP: MDPHnet Race-Ethnicity | Census Values |
|---|---|
| White | NHW = White, non-Hispanic |
| Black | NHB = Black, non-Hispanic |
| Asian | NHAP = Asian Pacific Islander, non-Hispanic |
| American Indian or Alaska Native | NHAI = American Indian, non-Hispanic |
| Hispanic | HISP = Hispanic |
| Unknown | No mapping available |