More than one conditions(AND) from the same field in set analysis

[aroyi]

Hi there,

I have patient admission data with all patients admission in a hospital,  each row represent an admission of a patient, with multiple columns(dimension) recording information about the patient. There's a column  named "treatment status" , it has 3 values: Start, progress, discharge. A patient can has one or multiple admissions(rows), with different treatment status each admission. I need to count  patients with treatment status has both "start" and "discharge". Here's my script:

Count({<TreatmentStage={"Start"}>*<TreatmentStage={"Discharge"}>}Distinct[ID])

The script shows no errors I get  0 count. Can you please tell me what's wrong? Thanks a lot!

[jonathandienst · Accepted solution]

Intersection is the right idea, but Your expression will return single lines with both values - impossible, so no results. You need to compare possibles with the P() operator

Count({<ID = P({<TreatmentStage={'Start'}>} ID)> * <ID = P({<TreatmentStage={'Discharge'}>} ID)>} Distinct ID)

[lblumenfeld]

The formula below will count each unique ID that has both a Start and Discharge record. (Check the case of my values and yours to ensure they're the same.)

Sum(Aggr(If(Count({< TreatmentStage= {"Start","Discharge"} >} TreatmentStage) > 1, 1, 0), ID))

Let me know if this works for you.

[aroyi]

Many thanks for your help,it's solved!

jontydkpi -Your script gets the correct total-24.

lblumenfeld-Your script gets total of 36-not sure what went wrong.