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: 
thomastc
Contributor III
Contributor III

Set analysis problem, pivot table expression, missing data.

This is my scenario:

I have a list of patients. Each patient has been enrolled on a trail. Each patient could have enrolled on a different date. We need to look at the number of admissions for each patient for the year before the trial started and the year following the trial. I have constructed some set analysis that does what I need and appears to work, it is used as the expression in a pivot table.

I tested the set analysis with an individual patient selected and got the results I expected. However when I removed the selection from the patients instead of seeing a populated pivot with all the patients in my group the table was empty. If I select an individual patient I see results... what am I doing wrong?

Here is the set analysis for getting the previous year:

=NumericCount({$<DischargeDate ={">=$(=date(AddYears(date(ServiceStartDate), -1), 'DD/MM/YYYY HH:MM:SS'))<$(=date(ServiceStartDate, 'DD/MM/YYYY HH:MM:SS'))"}>}DischargeDate)

The set analysis for getting the current year is:

=NumericCount({$<DischargeDate ={">=$(=date(ServiceStartDate, 'DD/MM/YYYY HH:MM:SS'))"}>}DischargeDate)

Can anyone explain why my pivot is empty unless I have a patient selected?

Thanks in advance!

1 Solution

Accepted Solutions
Not applicable

Set analysis is calculated once per chart, not once per row which is why it works fine when you have only one selected, but doesn't work when you have multiple patients in the chart.

What you need to do is something like:

=count(if([DischargeDate]>=date(AddYears(date(ServiceStartDate), -1) And [DischargeDate]<=date(ServiceStartDate, 'DD/MM/YYYY HH:MM:SS'), [DischargeDate]))

You might need to use aggr but off the top of my head I'm not sure. Also not sure how to change the text color...

View solution in original post

3 Replies
Not applicable

Set analysis is calculated once per chart, not once per row which is why it works fine when you have only one selected, but doesn't work when you have multiple patients in the chart.

What you need to do is something like:

=count(if([DischargeDate]>=date(AddYears(date(ServiceStartDate), -1) And [DischargeDate]<=date(ServiceStartDate, 'DD/MM/YYYY HH:MM:SS'), [DischargeDate]))

You might need to use aggr but off the top of my head I'm not sure. Also not sure how to change the text color...

thomastc
Contributor III
Contributor III
Author

Thanks for the response, I'll let you know how I get on.

thomastc
Contributor III
Contributor III
Author

That did the job thank you!