Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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)
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!
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)
...
A patient could have had Test2TypeFlag = 1 in the most recent year...
I tried this, but it doesn't seem to be working...
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)
This returns a result... But it's still counting patients more than once.
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)?
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.
BI Consultant