Qlik Community

Qlik Sense App Development

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

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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
Highlighted
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

View solution in original post

3 Replies
Highlighted
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

View solution in original post

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.