Solved

Summation of Yes/No Formula Field

Posted on 2011-02-15
19
401 Views
Last Modified: 2012-05-11
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
Comment
Question by:wdelaney05
  • 11
  • 6
  • 2
19 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 34903951
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 Comment

by:wdelaney05
ID: 34904002
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 Comment

by:wdelaney05
ID: 34904014
I keep getting an err msg stating "this field cannot be summarized". Any thoughts on that?
0
 
LVL 8

Expert Comment

by:kingjely
ID: 34904024
when you put {@COMPYES} in the detail section does it work, and come up with either 1's or 0's ?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34904065
Waht are these formulas

{@Hrs Per Order}  and  {@Compliant_Hrs_Per_Ord_Diff}

mlmcc
0
 

Author Comment

by:wdelaney05
ID: 34923146
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 Comment

by:wdelaney05
ID: 34923227
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 34923269
It can't because it uses a summary value in {@Total Hrs}

mlmcc
0
 

Author Comment

by:wdelaney05
ID: 34923288
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:wdelaney05
ID: 34923289
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
 
LVL 8

Expert Comment

by:kingjely
ID: 34923295
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 Comment

by:wdelaney05
ID: 34923309
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
 
LVL 8

Accepted Solution

by:
kingjely earned 250 total points
ID: 34923336
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
 

Author Comment

by:wdelaney05
ID: 34923349
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 Comment

by:wdelaney05
ID: 34923425
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
 
LVL 8

Expert Comment

by:kingjely
ID: 34923436
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 Comment

by:wdelaney05
ID: 34923498
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 Comment

by:wdelaney05
ID: 34923540
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
 
LVL 8

Expert Comment

by:kingjely
ID: 34923566
Ahaha, I didn't say you were being stupid, I tried did the same thing!!  :)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now