topic Disregarding current selections in AGGR function in QlikView

Disregarding current selections in AGGR function

Tue, 28 Apr 2009 03:09:16 GMT

Greetings.

I'm not even sure where to begin. I am trying to get a list of patient IDs and a total count of these IDs for patients that have visited the office between a range of dates (vDOSStart and vDOSEnd). I am trying to compare the count of visits to another variable, set to a minimum number of visits (vVisitMin). I need this count regardless of other current selections.

I have a variable, VisitSetList set to the following expression:

=CONCAT(DISTINCT AGGR( if(COUNT({1 <EncounterDate={">=$(vDOSStart)<=$(vDOSEnd)"} >} EncounterDate) >= $(vVisitMin), PatientID ), PatientID), ', ')

The resulting list of PatientID looks great, until I select a fields that disqualifies PatientID. I then loose that PatientID from my list in VisitSetList. I have tried a variety of set expressions, in an effort to include all PatientIDs, but my attempts result in either a null (I'm guessing a bad expression) or it has no effect.

If I'm AGGR on PatientID, is there any way to make it for all PatientID? Am I doing something stupid? Or is this a limitation of AGGR? Or is it because I am not using this function within a chart?

Thanks.

Disregarding current selections in AGGR function

Anonymous — Tue, 28 Apr 2009 03:21:39 GMT

From the little I know about the set analysis, the $ identifier makes it depending on selections. I'd try using 1 instead of $ for PatientID.

Regards,
Michael

Disregarding current selections in AGGR function

Oleg_Troyansky — Tue, 28 Apr 2009 03:27:36 GMT

Sally,

first of all - don't panic

Try the same formula, with the prefix ALL after DISTINCT:

=CONCAT(DISTINCT ALL AGGR( if(COUNT({1 <EncounterDate={">=$(vDOSStart)<=$(vDOSEnd)"} >} EncounterDate) >= $(vVisitMin), PatientID ), PatientID), ', ')

Oleg

Disregarding current selections in AGGR function

Tue, 28 Apr 2009 03:29:16 GMT

Add {1} as the set expression for CONCAT as well. See my example attached.

Disregarding current selections in AGGR function

Wed, 06 May 2009 04:14:49 GMT

Turns out none of your suggestions quite worked. However, you guys gave me a shove in the right direction. I changed my variable to the following:

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

And it worked. No longer using the AGGR function at all!

Jay - your qvw example helped a lot. For some reason, the "IF" in the calcuation was causing the results to always include only selected items -- it seemed to ignore the {1} in the sets.

Thanks for your help.

Sally