Ignore table dimension

Lauri — Mon, 20 Dec 2021 21:32:42 GMT

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.

Re: Ignore table dimension

sunny_talwar — Thu, 19 Mar 2020 19:54:13 GMT

May be try this

Count(DISTINCT PatientID)/Aggr(Count(DISTINCT PatientID), [Age Group])

Re: Ignore table dimension

Lauri — Thu, 19 Mar 2020 20:36:03 GMT

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!)

Re: Ignore table dimension

sunny_talwar — Fri, 20 Mar 2020 11:41:57 GMT

Oh ya, I totally forgot to add the NODISTINCT in there.. good catch...

topic Ignore table dimension in App Development

Ignore table dimension

Re: Ignore table dimension

Re: Ignore table dimension

Re: Ignore table dimension