OHDSI / OHDSI/WebAPI

Prevalence percentage filter not showing all values when set to 0%

Open
#1,808 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug cohort characterization
Dominant language
Java
Stars
151
Forks
183
Avg merge
14m
Merged PRs (30d)
2

Description

Expected behavior

Setting prevalence filter to 0% should show all values.

Actual behavior

For example: DemographicsAgeGroup, certain age groups are being filtered from the result because they are a relatively small proportion of the population.

Steps to reproduce behavior
  1. Create cohort with a few outlier age groups (either very young or very old
  2. Run Cohort characterization with demographics age group
  3. View results, note by default the 1% filter may omit the small age group
  4. Change the filter to 0%, and the results still filter

Example result from the UI and from the raw query:

UI:

Covariate name Covariate short name Count Percent
age group: 05-09 05-09 42154 26.53097
age group: 10-14 10-14 23775 14.96356
age group: 00-04 00-04 23284 14.65453
age group: 35-39 35-39 11439 7.199502
age group: 40-44 40-44 11130 7.005022
age group: 15-19 15-19 8981 5.65248
age group: 45-49 45-49 8244 5.188626
age group: 30-34 30-34 7697 4.844354
age group: 50-54 50-54 5791 3.644752
age group: 55-59 55-59 4621 2.908375
age group: 25-29 25-29 4021 2.530745
age group: 20-24 20-24 3880 2.442002
age group: 60-64 60-64 3607 2.270181

Raw Query:

cohort_definition_id covariate_name sum_value
13555 age group: 5 - 9 42154
13555 age group: 10 - 14 23775
13555 age group: 0 - 4 23284
13555 age group: 35 - 39 11439
13555 age group: 40 - 44 11130
13555 age group: 15 - 19 8981
13555 age group: 45 - 49 8244
13555 age group: 30 - 34 7697
13555 age group: 50 - 54 5791
13555 age group: 55 - 59 4621
13555 age group: 25 - 29 4021
13555 age group: 20 - 24 3880
13555 age group: 60 - 64 3607
13555 age group: 65 - 69 262

Note, the last age group (65-69) is missing from the exported results (the export results matches the UI).

Query (SQL Server dialect):

-- DemographicsAgeGroup
-- analysis_id = 3

-- Feature construction
with cteCovariates (covariate_id, cohort_definition_id, sum_value) AS (
	SELECT CAST(FLOOR((YEAR(cohort_start_date) - year_of_birth) / 5) * 1000 + 3 AS BIGINT) AS covariate_id, -- analysis_id = 3

		cohort_definition_id,
		COUNT(*) AS sum_value
	FROM ohdsi_results.cohort cohort
	INNER JOIN dbo.person
		ON cohort.subject_id = person.person_id
	WHERE cohort.cohort_definition_id IN (13555)
	GROUP BY cohort_definition_id,
		FLOOR((YEAR(cohort_start_date) - year_of_birth) / 5)
), cteCovRef (covariate_id, covariate_name, analysis_id, concept_id) AS (
	SELECT covariate_id,
		CAST(CONCAT (
			'age group: ',
			RIGHT(CONCAT('   ', CAST(5 * (covariate_id - 3) / 1000 AS VARCHAR)), 3),
			' - ',
			RIGHT(CONCAT('   ', CAST((5 * (covariate_id - 3) / 1000) + 4 AS VARCHAR)), 3)
			) AS VARCHAR(512)) AS covariate_name,
		3 AS analysis_id,
		0 AS concept_id
	FROM (
		SELECT DISTINCT covariate_id
		FROM cteCovariates
	) t1
)
select c.cohort_definition_id, cr.covariate_name, c.sum_value
from cteCovRef cr
join cteCovariates c on cr.covariate_id = c.covariate_id
ORDER BY c.sum_value desc;

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the cohort characterization steps with a 0% prevalence filter and compare the UI and exported results with the raw SQL query shown in the issue. Trace where the prevalence threshold is applied, then verify that small groups such as age 65-69 appear in both UI and export results when the filter is 0%.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
backend, databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.