Calculating population rates

Wed, 25 Nov 2020 16:16:04 GMT

HI all,

I'm a new user of Qlikview and first time poster.

I'm having trouble calculating unadjusted hospitalization rates (per 100,000). I have unit record data for episodes of care at a hospital to count up for the numerator. These unit records include information on year of admission, age at admission, sex and area of residence as well as a bunch of clinical variables. I have populations for each year, area of residence (about 500 of them), age and sex to sum up for the denominator. Here is part of my load script:

ADMISSIONS:

LOAD

ADM_YEAR AS YEAR,

AGE,

AREA_4DIGITCODE,

SEX,

CONDITION,

UNIQUE_ID

FROM

QVDdatafile(qvd);

POPULATIONS:

LOAD

YEAR,

AGE,

AREA_4DIGITCODE,

SEX,

Population

FROM

PopData.xlsx (ooxml, embedded labels, table is Sheet1);

Initially I was using the following expressions to calculate (1) number of hospitalizations, (2) population and (3) crude rate of hospitalization per 100,000 population:

(1) COUNT(UNIQUE_ID)

(2) SUM(Population)

(3) 100000*COUNT(UNIQUE_ID)/SUM(Population)

The results are fine if I make NO selections, such as for CONDITION or a specific YEAR. The Sum(population) correctly takes every population

However, when I want the rate of hospitalization rate for women who had a stroke in 2007, select 'Female' is a SEX item box, select 'Stroke' in a CONDITION item box and '2007' in a YEAR item box, only the populations in areas of residence where there was at least 1 stroke are summed. I understand and accept why this is the case.

I suspected i need a Set Analysis and tried this: COUNT(UNIQUE_ID)/SUM( {$<SA2_4DIGITCODE=> } Population) without success.

So i summary I want to calculate hospitalization rates for specific conditions/diagnoses within certain populations, such as Males in 2010, or Females in 2009. I would also like to get rates for groups of the AREA_4DIGITCODE, that we are calling REGIONS.

Can someone please direct me? I'm also open to suggestions on a better data model, though I can't give too much information about the data itself due to sensitivity of the data.

Cheers

Doug

topic Calculating population rates in QlikView

Calculating population rates