Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the following problem. I try to calculate the average number of secondary diagnoses in the patients, who are still staying in the hospital. Some of them do have a secondary diagnosis or two, some does not have any yet. I have to take all in-patients and all existing secondary diagnoses by these patients into account and calculate a mean value per patient per department.
The data model looks as follows:
let vMaxDate = num(today()) - 30; let vMinDate = $(vMaxDate) – 200; Dep: LOAD * INLINE [ Units Surgery Angiology Traumatology Obstetrics Nephrology ]; Let vDepCount=FieldValueCount('Units'); Diagnosis: LOAD * INLINE [ Code J10 K20 L30 M40 N50 ]; let vDiagCount = FieldValueCount('Code'); for i = $(vMinDate) to $(vMaxDate) let vAttDate = $(i); let vDuration = Round(Rand() * (25 - 1 ) + 1, 1); IF rand() > 0.3 THEN let vDisDate =$(vAttDate) + $(vDuration); ELSE vDisDate =; let vDuration = $(vMaxDate) - $(vAttDate); END IF for j = 0 to $(vDepCount) - 1 let vThisDep = Peek('Units', $(j), 'Dep'); let vN = $(i)* 1000 + $(j) + 100001; // Patient-ID. Patients: load '$(vN)' as PATID, date('$(vAttDate)') as AttDate, date('$(vDisDate)') as DisDate, $(vDuration) as Duration, '$(vThisDep)' as DEP autogenerate(1); IF rand() > 0.5 THEN for k = 1 to 3 let vD = floor(rand() * ($(vDiagCount))); ICD: load '$(vN)' as PATID, peek('Code', $(vD), 'Diagnosis') as ICDCODE, 'SEC' as ICDTYPE autogenerate (1); next k; END IF; next j; next i; drop tables Dep, Diagnosis; vDepCount=; vDiagCount=; vDatesCount=; i=; vAttDate=; vDuration=; i=; j=; vThisDep=; vN=; vD=; k=; vDisDate=; vMaxDate=; vMinDate=;
Now the diagramm to calculate the average:
Dimension is DEP.
This expression works, but it has two „if“ loops:
=avg( if(IsNull(DisDate), aggr( count(distinct if(ICDTYPE = 'SEC', ICDCODE)) , PATID) ))
This set expression however seems to calculate the average only on the patients who does have at least one secondary diagnosis (thus exclude those who does not have any):
= avg( {<PATID = E({1<DisDate={"*"}>} )>} aggr( count( {< PATID = E({1<DisDate={"*"}>} ), ICDTYPE={'SEC'} >} distinct ICDCODE), PATID) )
This works:
=sum( aggr( count( {< PATID = E({1<DisDate={"*"}>} ), ICDTYPE={'SEC'} >} distinct ICDCODE), PATID) ) / count( {$< PATID = E({1<DisDate={"*"}>} )>} distinct PATID )
However it is not very flexible if you want to calculate a median or fractile, for example.
There is a „compromiss“ solution, that works too:
=avg( {$<PATID = E({1<DisDate={"*"}>})>} aggr( count(distinct if(ICDTYPE = 'SEC', ICDCODE)), PATID) )
That is, outer set expression and inner if loop.
It seems, that the problem is the second set expression (inside the count). It seems to „communicate“ with the outer set expression (inside the avg).
Is there a way to produce the desired result with set expressions only?
(The adding of „, ICDTYPE=“ to the outer set expression does not change the behavior.)
Thank you very much in advance!