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

Disregarding current selections in AGGR function

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

4 Replies
Anonymous
Not applicable
Author

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Sally,

first of all - don't panic Wink

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

Not applicable
Author

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



Not applicable
Author

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