Selecting cases

[Developer1]

Within my data, I have individual account numbers- picture of tabular data is attached.  Each account number has had the opportunity to have 4 tests done (FER, GFR, HGB and TSAT).  Right now I am trying to identify each account number that has had all 4 tests done.  In this example, account number 100156 has had all 4 done, but 100175 and 100223 have not.  I tried a case statement (see below) to create the data item [Count Total], but it only tells me if all 4 tests were done for each individual occurrence of the account number rather than if all 4 were done for any occurrence of the account number.  Any suggestions for how to identify each account number that has had all 4 tests done?  Thank you.

CASE

WHEN ( [GFR Count] = 1 ) and ( [HGB Count] = 1 ) and ( [TSAT Count] =1 ) and ( [FER Count] = 1 ) then ( 1 )

else ( 0 )

END

[Developer1]

I tried to put it in table form here so people wouldn't have to open the image.

Account Number   Test Name   Test Done   GFR Count   HGB Count   TSAT Count   FER Count   Count Total
100156                   FER               1             0             0                     0                     1             0
100156                   GFR               1             1             0                     0                     0             0
100156                   HGB               1             0             1                     0                     0             0
100156                   TSAT              1             0             0                     1                     0             0
100175                   FER               1             0             0                     0                     1             0
100175                   GFR               1             1             0                     0                     0             0
100223                   GFR               1             1             0                     0                     0             0
100223                   HGB               1             0             1                     0                     0             0

[Lynn]

I tried to put it in table form here so people wouldn't have to open the image.

Account Number   Test Name   Test Done   GFR Count   HGB Count   TSAT Count   FER Count   Count Total
100156                   FER               1             0             0                     0                     1             0
100156                   GFR               1             1             0                     0                     0             0
100156                   HGB               1             0             1                     0                     0             0
100156                   TSAT              1             0             0                     1                     0             0
100175                   FER               1             0             0                     0                     1             0
100175                   GFR               1             1             0                     0                     0             0
100223                   GFR               1             1             0                     0                     0             0
100223                   HGB               1             0             1                     0                     0             0

You need to use an aggregate function with a "for" clause to define the scope. Your case statement is evaluating one row at a time so the condition will never be true for all four values on a single row.

total ( [GFR Count] + [HGB Count] + [TSAT Count] + [FER Count] for [Account Number] )

Try this calculation to see if you get the correct count per account.

[Developer1]

Hi! Thank you so much for the reply! I have tried that statement- unfortunately, it does not identify account numbers who have had at least one of each test run- it might identify someone with 4 GFR's, as an example, though which is not what I want.  I did find a solution using the following-

I created 4 separate filters:

total ( ( [GFR Count] ) for [Patient Account Number] ) >= 1
total ( ( [HGB Count] ) for [Patient Account Number] ) >= 1
total ( ( [TSAT Count] ) for [Patient Account Number] ) >= 1
total ( ( [FER Count] ) for [Patient Account Number] ) >= 1

So far, this seems to have worked, but I have not been able to return to this particular report in a few days.  I will comb through the results and make sure I am getting what I intended.

Thank you, though, for the suggestion!

[MFGF]

Hi! Thank you so much for the reply! I have tried that statement- unfortunately, it does not identify account numbers who have had at least one of each test run- it might identify someone with 4 GFR's, as an example, though which is not what I want.  I did find a solution using the following-

I created 4 separate filters:

total ( ( [GFR Count] ) for [Patient Account Number] ) >= 1
total ( ( [HGB Count] ) for [Patient Account Number] ) >= 1
total ( ( [TSAT Count] ) for [Patient Account Number] ) >= 1
total ( ( [FER Count] ) for [Patient Account Number] ) >= 1

So far, this seems to have worked, but I have not been able to return to this particular report in a few days.  I will comb through the results and make sure I am getting what I intended.

Thank you, though, for the suggestion!

The alternative would be to create a single filter with an expression that ANDs those four clauses

Eg

total ( ( [GFR Count] ) for [Patient Account Number] ) >= 1 AND
total ( ( [HGB Count] ) for [Patient Account Number] ) >= 1 AND
total ( ( [TSAT Count] ) for [Patient Account Number] ) >= 1 AND
total ( ( [FER Count] ) for [Patient Account Number] ) >= 1

Cheers!

MF

[Developer1]

Nice!  I did not realize that the "for" statements could be combined like that.

That is more efficient.

Thank you!