Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

OR Statement Within Set Analysis

I’m trying to create a chart that shows (by physician) what % of their patients had a certain test completed.

The fun part is that to qualify as having this test completed, a patient can fall into 1 (or more) of the following buckets:

  • TestType1Flag = 1 and TestDate within the last year
  • Test2TypeFlag = 1 and TestDate within the last 5 years
  • Test3TypeFlag = 1 and TestDate within the last 10 years

In addition, the time frame is selected by the end-user (Island_Date).

Currently, my chart has Dimension = Physician and the Expression is below.  The expression works, but if – for example – a patient had Test1 and Test2 in the correct time frames…  this patient is counted twice.  I understand why this is happening – I’m just unsure how to correct it.

---------------------------------------------------------------------------

Count (

{<

TestDate ={"<=$(=DATE(MAX(Island_Date)))>=$(=DATE(MIN(Island_Date)-365))"}

,

TestType1Flag = {1}

>}

DISTINCT Patient)

+

Count (

{<

TestDate ={"<=$(=DATE(MAX(Island_Date)))>=$(=DATE(MIN(Island_Date)-5*365))"}

,

Test2TypeFlag = {1}

>}

DISTINCT Patient)

+

Count (

{<

TestDate ={"<=$(=DATE(MAX(Island_Date)))>=$(=DATE(MIN(Island_Date)-10*365))"}

,

Test3TypeFlag = {1}

>}

DISTINCT Patient)

8 Replies
Not applicable
Author

Hi,

Maybe someting like this:

sum(

if(

TestDate <=DATE(MAX(Island_Date)) and TestDate >=DATE(MIN(Island_Date)-365))

and TestType1Flag = 1  ,1 ,

if(TestDate <=DATE(MAX(Island_Date)) and TestDate >=DATE(MIN(Island_Date)-5*365))

and Test2TypeFlag = 1 ,1 ,

if(TestDate <=DATE(MAX(Island_Date)) and TestDate >=DATE(MIN(Island_Date)-10*365))

and Test3TypeFlag = 1,1,0

)

)

You can only do this if you are sure that every row shows only one patient.

succes!

swuehl
MVP
MVP

At first glance, I think you need to specify time intervals that don't overlap in your sets, like (for the second part of the sum):

....

Count (

{<

TestDate ={"<$(=DATE(Min(Island_Date)-365))>=$(=DATE(MIN(Island_Date)-5*365))"}

,

Test2TypeFlag = {1}

>}

DISTINCT Patient)

...

Not applicable
Author

A patient could have had Test2TypeFlag = 1 in the most recent year...

Not applicable
Author

I tried this, but it doesn't seem to be working...

swuehl
MVP
MVP

Ah, sorry, I missed the different TestTypes.

Hm, maybe like

Count (

{<

TestDate = ({"<=$(=DATE(MAX(Island_Date)))>=$(=DATE(MIN(Island_Date)-365))"}*p({<TestType1Flag = {1} >}))

+

({"<=$(=DATE(MAX(Island_Date)))>=$(=DATE(MIN(Island_Date)-5*365))"}*p({<Test2TypeFlag = {1}>})

+

({"<=$(=DATE(MAX(Island_Date)))>=$(=DATE(MIN(Island_Date)-10*365))"}*p({<Test3TypeFlag = {1}>})

>}

DISTINCT Patient)

Not applicable
Author

This returns a result...  But it's still counting patients more than once.

swuehl
MVP
MVP

Are you sure? Since we use a distinct Patient count in one single count function, I can't think of how this could be.

Could you maybe create a small sample app and upload it here (upload is available via advanced editor)?

Miguel_Angel_Baeyens

Hi,

What about using three set modifiers in the same set analysis so what is counted in one case is not in the others? Something like

Count (

{<

TestDate ={"<=$(=DATE(MAX(Island_Date)))>=$(=DATE(MIN(Island_Date)-365))"}

,

TestType1Flag = {1}

>

+

<

TestDate ={"<=$(=DATE(MAX(Island_Date)))>=$(=DATE(MIN(Island_Date)-5*365))"}

,

Test2TypeFlag = {1}

>

+

<

TestDate ={"<=$(=DATE(MAX(Island_Date)))>=$(=DATE(MIN(Island_Date)-10*365))"}

,

Test3TypeFlag = {1}

>}

DISTINCT Patient)

The set analysis is considerably higher, but the different set modifiers are in the same expression and summed using the "+" set operator (logic OR).

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica