Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis - exclusions -- Can I do this better?


I have been slogging through set analysis for a while, and have a few items I suspect can be written easier than I have. I have two tables -- one is a table of patients, the other is a table of patients that should be excluded, based on MeasureType. In this example, I am just counting patients

vPatientCOUNT =

count( distinct { <$(vDenomPatSetList)> } PatientID)

vDenomoPatSetList =

PatientID = {'=COUNT( {1 <EncounterDate={">=$(vDOSStart)<=$(vDOSEnd)"},
PatDOB={">$(vAgeDateLower)<=$(vAgeDateUpper)"}> } EncounterDate) >= $(vVisitMin)'} - {$(vExcludeSetList)}

vExcludeSetList =

concat( distinct {$ <MeasureType = {'$(=TypeCode)'} > } PatientID, ',')

(Hopefully you can make some sense of this -- I've been breaking it down into variables so I can read the darned stuff!)

So -- I've determined that making a concat list can be a bad thing. I have tons of data, and it was really slowing things down. I've managed to get rid of most of my concated lists in this QVW, but how to remove the concat from vExcludeSetList is eluding me. Does anyone know how I can roll it up into vDenomoPatSetList and get rid of the concat?

Secondly, I printed out some documentation on Set Analysis that included the following:

sum ( {$<~Ingredient = {"*garlic*"}>} Sales)

returns the sales for the current selection, but with a forced exclusion of all ingredients containing the string 'garlic'.

I have tried using the tilde to move the MeasureType into the main section of code:

<~MeasureType = {'$(=TypeCode)'}

But I am getting really weird results -- its including only my excluded patients, regardless of the MeasureType (if its in that table, it gets counted), and no others. Basically the opposite of what I expected with the above description. What am I doing wrong?

I hope this all makes sense -- I'm having enough trouble wrapping my own head around it.

Thanks in advance.

5 Replies

If I understand correctly, you want to exclude from the count all patients where the MeasureType is the specific TypeCode? If so, I think you can use -= to exclude patients for that MeasureType without ever needing to manipulate a list of those patient IDs yourself, such as with the concat(). Something like this.

vDenomoPatSetList =

PatientID = {'=COUNT( {1 <EncounterDate={">=$(vDOSStart)<=$(vDOSEnd)"},


> } EncounterDate) >= $(vVisitMin)'}

MeasureType -= {'$(=TypeCode)'}

Not applicable

Initially, that worked -- I happened to have a database that had all nulls in MeasureType. But then I realized I always needed data in that field, and it no longer worked. I think I have a work around for that, and it is working on all databases.

However, I'm running into a similar problem with Date Deceased. This one is much more simple to demonstrate. I have one database that everyone is alive, another that has several deceased patients. In order to get an accurate count on each database, I ended up with two set equations (which is not practical -- I need one to work on both).

All patients are alive (all PatDeceasedDate is null):

=count( distinct {1<PatDeceasedDate -= {"<$(vDOSEnd)"}>} PatientID)

^ negative sign is about here

Some patients have a date in PatDeceasedDate:

=count( distinct {1-<PatDeceasedDate={"<$(vDOSEnd)"}>} PatientID)

^ negative sign is about here

Other than that, the equations are the same -- but running the first one in the second database, gets a count of 0.

Int this case, what I really need, is a way to qualify patients who either hae a deceased date of NULL, or the date is >= $(vDOSEnd). I was trying to do the negative, since that worked so well with MeasureType. What am I doing wrong?


Not applicable

Would your mind attaching you qvw, with or without data? Thanks.

Not applicable

This is the one with no Deceased Dates -- the blue boxes on the bottom have each equation.


Not applicable

Thank you. I will post if I figure out a solution. In the meantime I will learn advanced set analysis. Smile