topic OR Statement Within Set Analysis in QlikView

OR Statement Within Set Analysis

Tue, 22 Nov 2011 12:48:57 GMT

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)

OR Statement Within Set Analysis

Tue, 22 Nov 2011 13:02:07 GMT

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!

OR Statement Within Set Analysis

swuehl — Tue, 22 Nov 2011 13:05:44 GMT

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)

...

OR Statement Within Set Analysis

Tue, 22 Nov 2011 13:28:05 GMT

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

OR Statement Within Set Analysis

Tue, 22 Nov 2011 13:31:04 GMT

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

OR Statement Within Set Analysis

swuehl — Tue, 22 Nov 2011 13:38:57 GMT

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)

OR Statement Within Set Analysis

Tue, 22 Nov 2011 13:59:37 GMT

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

Re: OR Statement Within Set Analysis

swuehl — Tue, 22 Nov 2011 17:02:10 GMT

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)?

Re: OR Statement Within Set Analysis

Miguel_Angel_Baeyens — Tue, 22 Nov 2011 17:52:52 GMT

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