Jump to content
  • 0

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



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!

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...