Set analysis problem, pivot table expression, missing data.

[thomastc]

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!

[#2 · Accepted solution]

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]

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

[thomastc]

That did the job thank you!