topic Re: Relatively simple set analysis question in QlikView

Relatively simple set analysis question

thomastc — Wed, 15 May 2013 11:56:06 GMT

Hello folks,

This question is causing me some issues, I can get around it using an ‘if’ statement but would prefer to do it all using set analysis.

I need to identify patients who fall into a certain criteria in the last 12 months and have 2 or more events fitting that criteria, no problem so far.

However the problem arises with the requirement that they have to have had one of these events in the last 3 months.

I can get this to work but it still shows me patients where the activity is < 2; I only need to show activity where it is >=2.

Here is my set analysis so far:

=sum({<PseudoPID = {"=Count({1<PodC = {'NEL'}, LengthOfStay = {'>=2'}, MonthAbbrevList = {'>=$(=Num(Date#(Max(AddMonths(AddYears(MonthAbbrevList, -1), 1)), 'DD/MM/YYYY'))))<=$(=Max(MonthAbbrevList))'}>}GeneratedRecordIdentifier) >= 2"
,"=Count({1<PodC = {'NEL'}, LengthOfStay = {'>=2'}, MonthAbbrevList = {'>=$(=Num(Date#(Max(AddMonths(AddMonths(MonthAbbrevList, -3), 1)), 'DD/MM/YYYY'))))<=$(=Max(MonthAbbrevList))'}>}GeneratedRecordIdentifier) >= 1"
}
,MonthAbbrevList = {">=$(=Num(Date#(Max(AddMonths(AddYears(MonthAbbrevList, -1), 1)), 'DD/MM/YYYY'))))<=$(=Max(MonthAbbrevList))"},
       PodC = {'NEL'}, LengthOfStay = {'>=2'},
       PseudoPID = P(PseudoPID)
    >} Activity_Actual)

Simply put, get me patients who have had >=2 or more events in the last 12 months with one of those events in the last 3 months (from the latest date).

As stated it is solved using an ‘if’ statement but I would like to do it all with Set Analysis.

Thanks!

Re: Relatively simple set analysis question

sushil353 — Wed, 15 May 2013 12:03:25 GMT

Try this:

=sum({<PseudoPID = {"=Count({1<PodC = {'NEL'}, LengthOfStay = {">=2"}, MonthAbbrevList = {">=$(=Num(Date#(Max(AddMonths(AddYears(MonthAbbrevList, -1), 1)), 'DD/MM/YYYY'))))<=$(=Max(MonthAbbrevList))"}>}GeneratedRecordIdentifier) >= 2"
,"=Count({1<PodC = {'NEL'}, LengthOfStay = {">=2"}, MonthAbbrevList = {">=$(=Num(Date#(Max(AddMonths(AddMonths(MonthAbbrevList, -3), 1)), 'DD/MM/YYYY'))))<=$(=Max(MonthAbbrevList))"}>}GeneratedRecordIdentifier) >= 1"
}
,MonthAbbrevList = {">=$(=Num(Date#(Max(AddMonths(AddYears(MonthAbbrevList, -1), 1)), 'DD/MM/YYYY'))))<=$(=Max(MonthAbbrevList))"},
       PodC = {'NEL'}, LengthOfStay = {">=2"},
       PseudoPID = P(PseudoPID)
    >} Activity_Actual)

HTH

Sushil

Re: Relatively simple set analysis question

thomastc — Wed, 15 May 2013 12:23:03 GMT

That's red line city I am afraid.

I suppose we can simplify this just by taking into account the date ranges and the counts. I can add the other criteria later.

If we just say we want a count of 2 or more in the last 12 months with one (or more) of the events with in 3 months of the latest date.

Thanks,

Tom

Re: Relatively simple set analysis question

thomastc — Wed, 15 May 2013 18:07:36 GMT

Well I think I have sorted it now using the power of ‘set intersection’:

=sum(
{1<FullDate = {">=$(=AddMonths(AddYears(Date(Max(MonthAbbrevList),'DD/MM/YYYY'), -1), 1))<=$(=MonthEnd(Date(Max(MonthAbbrevList),'DD/MM/YYYY')))"}
, PodC = {'NEL'}, LengthOfStay = {'>2'},PseudoPID =
           P({1<PseudoPID = {"=Count({<LengthOfStay = {'>2'}, PodC = {'NEL'}, FullDate = {'>=$(=AddMonths(AddYears(Date(Max(MonthAbbrevList),'DD/MM/YYYY'), -1), 1))<=$(=MonthEnd(Date(Max(MonthAbbrevList),'DD/MM/YYYY')))'}
           >}GeneratedRecordIdentifier) >= 1"}>} PseudoPID)
           *
           P({1<PseudoPID = {"=Count({<LengthOfStay = {'>2'}, PodC = {'NEL'}, FullDate = {'>=$(=AddMonths(AddYears(Date(Max(MonthAbbrevList),'DD/MM/YYYY'), -1), 1))<=$(=MonthEnd(Date(Max(MonthAbbrevList),'DD/MM/YYYY')))'}
           >}GeneratedRecordIdentifier) >= 2"}>} PseudoPID)
>} Activity_Actual)