OR Statement Within Set Analysis

[#1]

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)

[#2]

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]

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)

...

[#4]

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

[#5]

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

[swuehl]

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)

[#7]

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

[swuehl]

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