Pivot table calculated field not counting text string correctly.

[roattw]

Basic pivot table with a basic DP report.  Trying to use a calculated field to count total entries by day and how many were flagged as YES. The answer would be 17 entries, 4 Yes.   The calculation field will only count total entries (17) for a formula that is supposed to only count value = "Yes" (4).  Formula verifies correctly.  Of note, same results using regular report.

 

 

Report formula setup and returned results:

SELECT COUNT (Call_No) FROM Count_treated_by_date                                                        SELECT COUNT (Call_No) FROM Count_treated_by_date WHERE Tx_yes='Yes'

 

As always, all help is appreciated!

 

[CoopperBackpack]

Hello @roattw,

To get an accurate result, please use the 'target' keyword to associate the formula with each row. In this case, please select the 'Custom' option.

1) SELECT COUNT (Call_No) FROM Count_treated_by_date WHERE Pt_Date=target.[@field:Pt_Date]

2) SELECT COUNT (Call_No) FROM Count_treated_by_date WHERE Tx_yes='Yes' AND Pt_Date=target.[@field:Pt_Date]

 

[roattw]

Thank you.  That resolved the issue!