# Summation of Yes/No Formula Field

Hi,
I have a formula field in my Details section called _Compliant. _Compliant's formula is:
If
{@Hrs Per Order} <= {@Compliant_Hrs_Per_Ord_Diff}
Then "Yes"
Else "No"

I need to total the number of yes's and no's in two new fields. Total Compliant = total count of all yes's
Total Non Compliant = total count of all no's
How do I do this?

###### Who is Participating?

Commented:
One way i use is the add command,

SELECT field1,field2,field3,field4,field5,
sum(whatever), sum(whatever) ect
make sure you get the correct results in sql when you excecute.

Then go to database>database expert> Choose your connection > and go to addcommand.

and put the 'SELECT' in.

Rename, Addcommand to 'COMPLAINTS' as a new table.

Then you have a new table with only fields you need, with the summing already done in the back end,
Then put the fields on the report, and do your summing on the fields as they are just fields at that point.

It's alot quicker as you have only the fields you need, and a bit more flexible, ie you can sum fields easier ect.

It's a bit more of a pain to get into the habit of this, but its the way to go when you get into more complicated reporting.

Hope that helps
0

Commented:
Hi there,

Make a new formular called
{@COMPYES}
and put in

if { @_Compliant} = "Yes" then 1 else 0

Put that in the details section,
Then go to 'Insert summary' (on the toolbar) and sum  {@COMPYES}
You can then righ click on the  {@COMPYES} field and suppress it

DO the same for the No's

0

Author Commented:
Ok, I created a new formula named {@COMPYES}. I went to Insert/Summary but COMPYES wasn't in the list of available fields for summing. How can I manually sum this formula?
I guess I can make another formula named SumCOMPYES that does this: SUM({@COMPYES}).
0

Author Commented:
I keep getting an err msg stating "this field cannot be summarized". Any thoughts on that?
0

Commented:
when you put {@COMPYES} in the detail section does it work, and come up with either 1's or 0's ?
0

Commented:
Waht are these formulas

{@Hrs Per Order}  and  {@Compliant_Hrs_Per_Ord_Diff}

mlmcc
0

Author Commented:
Hi! Sorry I left the question, many apologies! Our network went down yesterday and today I was trying to catch up to high priority tasks. I'm afraid I put my EE question in lower priority tasks for the day...

Ok, so, kingiely, to answer your question "when you put {@COMPYES} in the detail section does it work, and come up with either 1's or 0's ?"
Yes, it does.

mlmcc, to answer your question "what are these formulas {@Hrs Per Order}  and  {@Compliant_Hrs_Per_Ord_Diff} ?"

{@Hrs Per Order} : {@Total Hrs}/{@Ttl Restraints Ordered}
{@Total Hrs}: SUM({@Sum Hrs})
{@Sum Hrs}: ({Command.SEG_END}-{Command.SEG_START})*24

{@Ttl Restraints Ordered}: DistinctCount({Command.ORDER_PROC_ID})

{@Compliant_Hrs_Per_Ord_Diff}: {@Total Hrs}/{Command.EXPECTED_HRS}
0

Author Commented:
ok, here's what I tried: I attempted to make a workaround. I changed _Compliant formula to this:
If
{@Hrs Per Order} <= {@Compliant_Hrs_Per_Ord_Diff}
Then 1
Else 0

Then I made a new formula called Compliant Display. Compliant Display has this formula:
if {@_Compliant} = 1 then "Yes"
else "No"

Compliant Display will display in the Details section for the appropriate field. Now, I just have to make the summation of  _Compliant work. I still get an err msg stating that it cannot be summed.
0

Commented:
It can't because it uses a summary value in {@Total Hrs}

mlmcc
0

Author Commented:
So, you can't sum something that already uses a summary value? Darn. How do I get around this?
I thought may be it was because I didn't have a If Not Is Null kind of missing statement for _Compliant...
0

Author Commented:
So, you can't sum something that already uses a summary value? Darn. How do I get around this?
I thought may be it was because I didn't have a If Not Is Null kind of missing statement for _Compliant...
0

Commented:
You cant sum a sum, atleast that way. is why you are getting the error.
Can you do that formular in whatever database you are using, and then just pass the field to the report and then do the sum ?

Ie do the sum formular in the back end eg sql, then put the resulting field on the report, and do the summary?
0

Author Commented:
kingiely, re: ie do the sum formular in the back end eg sql, then put the resulting field on the report, and do the summary?

Yup, I guess I can do that...
0

Author Commented:
kingiely, that's really nice advice, thanks. Much obliged.

Well, I guess I'm gonna revisit my SQL, thanks to both of you!
0

Author Commented:
Actually, after doing some experimentation, I did this:

if {@Compliance Display} = "Yes"
Then COUNT({Command.Pat_ID})

and a Sum NonCompliant formula:
if {@Compliance Display} = "No"
Then COUNT({Command.Pat_ID})

That seems to work. Eeks, I think I'm officially obsessed with this!
0

Commented:
I think you will find that counts all records, not just the 'No''s or Yes's, I tried that first.

saying if it finds ANY or even 1 Compliance Display that = "Yes"
Then COUNT all Command.Pat_ID's

You may want to double check but thats my understanding

0

Author Commented:
Hmm. I tried it with one sample record. the sample record was non compliant. The Sum NonCompliant field has a 1. The Sum Compliant field has a 0. If you're right kingiely, wouldn't both be 1, then?
0

Author Commented:
Actually, no, both wouldn't be one because there aren't any No's yet since I have only one record. You're right kingiely, I was just being stupid.
0

Commented:
Ahaha, I didn't say you were being stupid, I tried did the same thing!!  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.