Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

That did the job thank you!