Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Relatively simple set analysis question

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!

1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

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)

View solution in original post

3 Replies
Highlighted
Master II
Master II

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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)

View solution in original post