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
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
Quote from: Developer1 on 02 Mar 2019 09:22:27 AM
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.
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!
Quote from: Developer1 on 07 Mar 2019 03:15:10 PM
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
Nice! I did not realize that the "for" statements could be combined like that.
That is more efficient.
Thank you!