[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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?


0
wdelaney05
Asked:
wdelaney05
  • 11
  • 6
  • 2
2 Solutions
 
kingjelyCommented:
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
 
wdelaney05Author 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
 
wdelaney05Author Commented:
I keep getting an err msg stating "this field cannot be summarized". Any thoughts on that?
0
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
kingjelyCommented:
when you put {@COMPYES} in the detail section does it work, and come up with either 1's or 0's ?
0
 
mlmccCommented:
Waht are these formulas

{@Hrs Per Order}  and  {@Compliant_Hrs_Per_Ord_Diff}

mlmcc
0
 
wdelaney05Author 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
 
wdelaney05Author 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
 
mlmccCommented:
It can't because it uses a summary value in {@Total Hrs}

mlmcc
0
 
wdelaney05Author 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
 
wdelaney05Author 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
 
kingjelyCommented:
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
 
wdelaney05Author 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
 
kingjelyCommented:
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
 
wdelaney05Author 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
 
wdelaney05Author Commented:
Actually, after doing some experimentation, I did this:
Made a Sum Compliant formula:

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
 
kingjelyCommented:
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
 
wdelaney05Author 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
 
wdelaney05Author 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
 
kingjelyCommented:
Ahaha, I didn't say you were being stupid, I tried did the same thing!!  :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 11
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now