Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
aroyi
New Contributor III

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

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!

1 Solution

Accepted Solutions
MVP
MVP

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

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
3 Replies
MVP
MVP

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

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
lblumenfeld
Contributor

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

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
New Contributor III

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

-Your script gets the correct total-24.

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