Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I suspect I'm going to have a "D'oh!" moment when someone gives me the solution...
I have a data table of patients with dimensions PatientID, AgeGroup.
I have a data table of conditions with dimensions PatientID, Condition. A subset of all patients are in it.
I created a straight table with dimensions AgeGroup and Condition; the measure is the % of all patients who have each Condition. For example, "Asthma" is a Condition, so the result should be:
AgeGroup | Condition | %ofPatients |
0-17 | Asthma | 4% |
The calculation is:
Count(distinct PatientID)/Count(Total <[Age Group]> Distinct PatientID)
My problem: When I uncheck 'Include null values' for Condition, the denominator includes only patients who have one of the conditions. But I need it to include ALL patients, including those not in the Conditions data table. I want to know what percent of all of the kids have asthma, what percent have diabetes, etc.
Am I forced to include null values for Condition for the calculation to be correct?
I tried replacing Total with All. Didn't help. I tried adding all PatientID values to the Conditions data table with a value of 'NA' in the Condition field. That works mathematically, but having a row for 'NA' is totally confusing for users.
May be try this
Count(DISTINCT PatientID)/Aggr(Count(DISTINCT PatientID), [Age Group])
May be try this
Count(DISTINCT PatientID)/Aggr(Count(DISTINCT PatientID), [Age Group])
Yes! I just had to add NODISTINCT to the Aggr function.
Thanks so much. (But I didn't slap my forehead with a D'oh! -- this wasn't obvious to me!)
Oh ya, I totally forgot to add the NODISTINCT in there.. good catch...