Calculated field not returning expected value for a basic WHEN field=X

[roattw]

Have a very basic table of data with three fields: 

Field 1:  timestamp date field when entry made (Patient_date)
Field 2:  an autonumber entry ID (MD_ID)
Field 3:  text field from a  custom dropdown choice of Yes or No (Treated)

Basic pivot table to show by day number of entries made (MD_ID not blank) and number of those entries that day that had a value of Yes for "Treated":

Column:  date
Calculated Field 1:  number of entries that day
Calculated field 2:  number of entries = Yes that day

But I cant get the calculated field formula (that verifies fine) to actually count only the Yes values.    Seems to count total regardless of the WHERE = Yes statement.

Missing something basic I suspect.

The Data:

The DP and Results:

Many thanks, as always!

[GoodBoy]

Hi @roattw, may I know what is the data type of your Treated column? Is it a Text(255) or a checkbox?

 

If it is a checkbox, I believe you should use 1/0 for their comparison. 1 if Yes and 0 if No.

[roattw]

Thanks for the reply!  Its just a text field.

[cheonsa]

Hello @roattw,

In the Summarize values by, change it to Custom instead of Count.

 

Hope this works.

[CoopperBackpack]

Hello @roattw,

As I see the reason for the issue is related to the SELECT statement as well.

 

You use this statement: SELECT COUNT(Treated) FROM Patient_Encounters WHERE Treated = 'Yes'

And it counts all the records in the table with the 'Yes' value.

 

But the goal is to count the records with the 'Yes' value for the specific date. 

So, the example of the statement you need to use is: SELECT COUNT(Treated) FROM Patient_Encounters WHERE Treated = 'Yes' AND Patient_Date = '01/01/2022'

Of course, we don`t need to hardcode the date. And this is why we need to use a parameter that will be replaced by the required date in each row. 

The syntax is:  SELECT COUNT(Treated) FROM Patient_Encounters WHERE Treated = 'Yes' AND Patient_Date = target.[@field:Patient_Date]

 

Hope this helps.