Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

More fun with set analysis -- Restricting chart data

Greetings.

I am trying to build a table of patient information. I initially started with a table box, but it always displayed all patients in the table, and not a subset of them. So I have a chart, instead. The chart expressions are the various columns I wish to display, and it has a single dimension, as follows:

=if(MATCH(PatientID, $(=concat(distinct { PatientID * $(vDenomPatSetList) } PatientID, ', ')))>0, PatientID)

vDenomPatSetList is a <PatientID = > set. This results in a chart table, with only the patients that exist in the vDenomPatSetList set. However, in some cases, the vDenomPatSetList set has *a lot* of patients, and this is taking a while, because of the concat. Is there any way to remove the contcat, and have my table display just those patients that qualify as part of the vDenomPatSetList set?

Thanks!

11 Replies
Anonymous
Not applicable
Author

Sally,
I think that it's better to reslove by creating an additional list of Patients (the ones who meet the conditions) in the data model rather than applying conditions in the charts.

Not applicable
Author

That's how I originally wrote this QVW, but it required a reload every time certain inputs changed. Can't run an on-demand reload on a Server, and no one is happy with the Client version I have.

I am trying to rewrite the QVW using set analysis so it doesn't have to be reloaded each time input is changed. The solution I have is dog slow, but its faster than trying to re-run the load. I was hoping for some direct way to "see" the set without having to concat the contents.

johnw
Champion III
Champion III

I would think that whatever is used to build the vDenomPatSetList, just use the same thing inside your set analysis expression. The devil is in the details, but I'm not sure I can get more specific without knowing how the vDenomPatSetList is built.

Not applicable
Author

Heh.

vDenomPatSetList = <PatientID = {'=COUNT( {1 <EncounterDate={">=$(vDOSStart)<=$(vDOSEnd)"},
PatDOB={">$(vAgeDateLower)<=$(vAgeDateUpper)"},
$(vGenderSet)
$(vMeasureSet)
PatReportExempt = {"$(vPatExemptSetList)"}> -1<MeasureType = {"$(=TypeCode)"}> -1<PatDeceasedDate= {"<$(vDOSEnd)"}>} EncounterDate) >= $(vVisitMin)'}>

(it makes my head hurt every time I read it -- but it works)

Basically, what I want, is a dimension that was:

= {$(vDenomPatSetList)} PatientID

But it doesn't seem to allow that kind of logic in the chart dimension? If I could just get rid of the "concat", I know things would be speedy.

Anonymous
Not applicable
Author

I don't think it's possible to undersatnd without seeing the whole picture - it refers to other variables which are also enigma...
What is important to understand is the content of this variable. If it's a list of Patient Id's, the expression could be
if(index(vDenomPatSetList, PatientID)>0, PatientID)

johnw
Champion III
Champion III

You mention the inputs changing as why you can't do this in the script. So are the users entering these input variables? If so, aren't the input variables just being used instead of making selections? Why not just make selections? A lot of these look like ranges, which lend themselves to multi-value sliders for instance. Or if you want the data entry still done as is, you could use a macro to set the real field values from the input variables.

If you must do this without making actual selections... hmmm... one of the details I missed in my first read was that you were using this as a calculated dimension, which doesn't allow set analysis directly. So you can't just stick the set analysis expression in like I was thinking.

But I don't think you need a calculated dimension. Just use PatientID as the dimension. Then, where you might currently have sum(FieldA) as an expression, use sum({$(vDenomPatSetList)} FieldA) instead and exclude nulls. Seems like it would work.

Not applicable
Author

Michael - vDenomPatSetList can look like this, for example:

<PatientID = {'=COUNT( {1 <EncounterDate={">=05/01/2008)<=04/30/2009"},
PatDOB={">05/01/1932<=05/01/1990"},
PatReportExempt = {"*"}> -1<MeasureType = {"DM-5"}> -1<PatDeceasedDate= {"<04/30/2009"}> } EncounterDate) >= 1'}>

John - I could use sum({$(vDenomPatSetList)} PatientID), I guess, if I applied it to every column (all of the expressions). I have 16 columns. I was afraid of calculating that 16 times for each row may be more overhead than just one calculation on the PatientID (one dimension). I loved the option on the Table Box to exclude row if value is null, but I can't find anything like that for charts. The "Suppress Missing" on the Chart options seems to only apply if *all* of the expressions are null. I guess I could try it, and see if it runs faster than the MATCH command I'm currently using in the dimension.

Its kind of hard to explain why I'm not using selections. This qvw is to measure how well a provider is managing their patient base, in this case, with diabetes. How well he's treating the diabetes. There is a group of patients that is a denominator -- all of the patients who are diabetic. The, for each test, there are patients that qualify as the numerator. Once the overall denominator and numerator patients are displayed, then the user can further refine the results by clicking on normal stuff. So I need to keep tabs on the original group of patients that qualified and the group of patients the user has futher narrowed down. If I used normal selections for the overall counts, even if I locked in the selections so the user couldn't un-select them, I had a problem trying to differentiate in my patient evaluation which selections to ignore and which to apply, based on whether I was trying to display the overall group, or the current refined group. Am I making any sense at all? Trying to use QV for clinical medial information has been a challenge.

So, for the overall numbers, I'm trying to use set analysis. Originally, I forced a load whenever they changed any of the "denominator" criteria, which worked great. Then all I had to do was save the inital numbers from the load, and display whatever they selected.

johnw
Champion III
Champion III

I think it's at least worth comparing the calculation speed of doing it in the dimension vs. in the expression (calctime under sheet properties -> objects). Set analysis is pretty fast, but I agree that doing it 16 times may slow you down a lot, so only testing would let you know for sure.

Actually, I'm not sure the two approaches will give the same results. As is, I believe you start with the list of numerator Patient IDs, then check if they're on the denominator list of Patient IDs. So I believe your chart would only be the intersection. If you instead move the set analysis to the expressions, I believe you'll get the full denominator list, not the intersection. I gather you want the intersection, so what I suggested probably won't work.

As another way of keeping track of what are essentially two sets of selections, how about loading in two distinct sets of data? With a denominator set of data, you could either use regular field selections, or set a list of DenomPatientID using a macro from the input variables. Then expressions dealing with the denominator would use denominator fields, and expressions dealing with the numerator would use numerator fields. The performance downside would be if you need to link the two together in charts at a detailed level, as you'd probably end up with expressions like sum(if(PatientID=DenomPatientID,FieldA)), which is very slow.

This may not be applicable, but one way of doing an intersection of two (or more) data sets is to add "add to memory" and "clear memory" buttons to your application. So in this case, make the selections that correspond to one set, say the denominator set. Hit "add to memory". The macro for the button selects all patients matching the current selections and locks them. Clear the selections. The original patient list remains intact. Now make a completely different set of selections. The result will be the intersection of the two sets of selections. In your case, it doesn't seem like you want something THAT dynamic, so it probably doesn't apply, but I thought I'd toss it out there as well.

If I'm right about you wanting the intersection, one approach would be to turn your set analysis expression into an IF for the calculated dimension. So rather than check that you're on the list, just replicate all the conditions. Something like this, though I'm sure I've totally gotten the details wrong:

if(count(if(EncounterDate>=$(vDOSStart)
and EncounterDate<=$(vDOSEnd)
and PatDOB>$(vAgeDateLower)
and PatDOB<=$(vAgeDateUpper)
and $(vGenderSet)
and $(vMeasureSet)
and match(PatReportExemp,$(vPatExempSetList)
and MeasureType<>TypeCode
and PatDeceasedDate<$(vDOSEnd),EncounterDate)>=$(vVisitMin), PatientID)

Again, it might not be any faster than what you have, but it's another way to express it, so another approach to performance test.

Anonymous
Not applicable
Author

Sally,
Regarding the "I loved the option on the Table Box to exclude row if value is null, but I can't find anything like that for charts. The "Suppress Missing" on the Chart options seems to only apply if *all* of the expressions are null. " - did you think of using calculated dimensions in the chart? With "suppress null values". I'm not sure it will be any faster.

John,
I was working on an old application today, it was created long before set analysis, and required some performance improvement. I replaced slow expressions with the set analysis, and it takes now more memory and leads to "out of memory" messages. It well maybe that your expression with IF is preferable here.