Case Statement with Count Distinct

user028582

Hi I am trying to write a case statement to find how many individuals are admitted (so admitted = admission date present AND discharge date and decreased dates are blank). I have this so far:

Count (DISTINCT
Case when (STATUS = 'Admitted')
AND DISCHARGEDDATE IS NULL
AND DECEASEDDATE IS NULL
THEN IDFFORMID
END

I want to count the distinct IDFFORMID only when the above conditions are present

GrantSmith

Yes, you'd need to utilize a group by function. You can either use a function in the group by or use a formula tile to put your CASE statement in and then do a distinct count with a group by tile.

GrantSmith

Are you keying your admitted based off of STATUS or and ADMITDATE field?

What issues are you running into with your beast mode?

It looks like you're missing the trailing ) in your beast mode after the END.

user028582

I was trying to do the calculation in a formula tile on an ETL but I just read that I need to do it in a group by tile. Trying that now. I was basing the status off a status filed however sometimes that field is not updated when a discharge or death occurs so I am trying to get a true census based on people admitted who are not discharged or died.

GrantSmith

Yes, you'd need to utilize a group by function. You can either use a function in the group by or use a formula tile to put your CASE statement in and then do a distinct count with a group by tile.