Case Statement with Count Distinct

user028582
user028582 Member
in Beast Mode

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

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user028582
    user028582 Member

    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
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories