Jump to content
  • 0

Pivot table calculated field not counting text string correctly.


roattw
 Share

Question

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.

 

caspio_1.png.9de07d3b2a367ef442738f24f87f6d98.png

 

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'

caspio_4.thumb.png.3e75eac1046a599a49259102b0094b45.png

Caspio_3.png.ec7ef686c81a6450bb9e37327f220bbb.png

 

As always, all help is appreciated!

 

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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]

K5jJeN8.png

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

b1DcCwZ.png

 

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.

Guest
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.

Loading...
 Share

×
×
  • Create New...