Smart questions
Smart people
 Find A ForumFind An Expert
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Remember Me

Are you a
Computer / IT professional?
Join Tek-Tips now!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

#### Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

#### Feedback

"...keep up the good work with this forum, I think this is the best one around. ...you actually try to help people learn for themselves. ...I commend you on providing a very good, open learning atmosphere, where usually egos are left behind..."

#### Geography

Where in the world do Tek-Tips members come from?

# Running Total Based on Multiple Formulas

 Forum Search FAQs Links Jobs Whitepapers MVPs
 LisaRR (TechnicalUser) 7 Mar 12 8:18
 Hello, I have been looking at previous posts but don't see anything that fits my specific scenario.  Can anyone help with this?I need to count a record if it meets 4 specific criteria.  I have tried writing the formulas separately and bringing them into a running total using "Evaluate using a formula".  I also tried writing one formula statement in "Evaluate using a formula" but that is also not working.  I think I may need to create a variable statement.  If that is the case, could someone provide some direction? Here are my formulas written in one combined formula statement:(If{TABLE.ID} = "5987" then {TABLE.VALUE} = "No") and (If{TABLE.ID} = "5986" then {TABLE.VALUE} in ["No","Unknown"]) and(If{TABLE.ID} = "5988" then {TABLE.VALUE} = "No contraindications") and(If{TABLE.ID} = "5989" then {TABLE.VALUE} = "No exclusions")If each of the above statements is true, I need to count the record.  If any of them is false, I do not want to count the record.My report is grouped by patient ID.  I've tried placing the running total in the group footer and the report footer - it returns 0.  It does work if I include only one of the formulas - when I combine them it doesn't work.  Any suggestions?
 kray4660 (MIS) 7 Mar 12 10:31
 Remember that "Evaluate using a formula" requires a boolean result (which you have, whether you realize it or not).Basically what your formula is saying.  If the the {TABLE.ID} = 5987 then true if {TABLE.VALUE} = "No" (remember that an equals sign is a compassion not an assignment).  So if you follow this logic down, the only way you will get a count if the {TABLE.ID} is multiple value and you have all the {TABLE.VALUE} values in one record.I am not sure what you trying to accomplish with the running total.  So if you can give some details, we will try to assist you.I hope this helps.
 LisaRR (TechnicalUser) 7 Mar 12 11:22
 Thanks for your response.  I realize I wrote boolean formulas, I want to count the record if the results of all of those formulas are "True".  You are correct that Table.ID is a multiple value.  The report should count patients eligible to receive a flu vaccine.  Here is a sample of the data as it displays in the details of the report.  Each answer is associated with a patient ID.  I want to count the patient id when it meets the criteria I stated in the initial post.ID TABLE.ID  TABLE.VALUE5987      No5987      Yes          5986      Unknown5986      Yes        5988      No contraidications5988      Allergic to eggs 5989      No exclusionsDoes this provide enough additional information?  Thanks so much for you assistance.
 kray4660 (MIS) 7 Mar 12 12:28
 Try this:(If ({TABLE.ID} = "5987" and {TABLE.VALUE} = "No") or({TABLE.ID} = "5986" and {TABLE.VALUE} in ["No","Unknown"]) or({TABLE.ID} = "5988" and {TABLE.VALUE} = "No contraindications") or({TABLE.ID} = "5989" and {TABLE.VALUE} = "No exclusions")
 kray4660 (MIS) 7 Mar 12 12:29
 I hit the submit too soon.Try this:(If ({TABLE.ID} = "5987" and {TABLE.VALUE} = "No") or({TABLE.ID} = "5986" and {TABLE.VALUE} in ["No","Unknown"]) or({TABLE.ID} = "5988" and {TABLE.VALUE} = "No contraindications") or({TABLE.ID} = "5989" and {TABLE.VALUE} = "No exclusions") then trueelse false
 LisaRR (TechnicalUser) 7 Mar 12 12:46
 It does work using "or" except it is not giving me the right results.  I need to count them if they meet all criteria, not just one.  I changed the "or" to "and" but then the running total returns only zeros.  Here is a the formula I wrote:({IP_FLWSHT_MEAS_influenza.FLO_MEAS_ID} = "5987" and {IP_FLWSHT_MEAS_influenza.MEAS_VALUE} = "No") and({IP_FLWSHT_MEAS_influenza.FLO_MEAS_ID} = "5986" and {IP_FLWSHT_MEAS_influenza.MEAS_VALUE} in ["No","Unknown"]) and({IP_FLWSHT_MEAS_influenza.FLO_MEAS_ID} = "5988" and {IP_FLWSHT_MEAS_influenza.MEAS_VALUE} = "No contraindications") and({IP_FLWSHT_MEAS_influenza.FLO_MEAS_ID} = "5989" and {IP_FLWSHT_MEAS_influenza.MEAS_VALUE} = "No exclusions")
 kray4660 (MIS) 7 Mar 12 16:11
 The and's will not work properly at all since it is requiring that {IP_FLWSHT_MEAS_influenza.FLO_MEAS_ID} have multiple values per record.In an after thought I did not need to use an If statement (used your original field names for simplicity):({TABLE.ID} = "5987" and {TABLE.VALUE} = "No") or({TABLE.ID} = "5986" and {TABLE.VALUE} in ["No","Unknown"]) or({TABLE.ID} = "5988" and {TABLE.VALUE} = "No contraindications") or({TABLE.ID} = "5989" and {TABLE.VALUE} = "No exclusions") This formula will evaluate to true if {TABLE.ID} = "5987" and {TABLE.VALUE} = "No" OR {TABLE.ID} = "5986" and {TABLE.VALUE} in ["No","Unknown"] OR {TABLE.ID} = "5988" and {TABLE.VALUE}= "No contraindications") OR {TABLE.ID} = "5989" and {TABLE.VALUE} = "No exclusions"I hope this helps.  And it very important to use the parenthesis as shown.
 LisaRR (TechnicalUser) 7 Mar 12 16:30
 I'm not sure the OR statement will work - isn't this saying condition 1 is True OR condition 2 is True OR etc...  Or am I misunderstanding how this OR statement works? A patient can meet one of the critieria but then have an exclusion so they are not eligible for the vaccine.  All conditions have to be true.  Will this formula do that?  Thanks again for your time, I appreciate it.
 kray4660 (MIS) 7 Mar 12 16:42
 So are you saying at after reading all the records (since one record could not have all the conditions), then it would be determined if a patient is eligible for a vaccine (I think I might have skipped a post in the beginning)?If so, I am sure it is possible with using a shared variable as a flag and doing some checking at the record level. I will think about a solution.Of course anybody else in the forum with a solution, I will gladly let you take the credit.
 LisaRR (TechnicalUser) 7 Mar 12 16:44
 Yes, that's right.  I need to evaluate all the records for the condition to determine vaccine eligibility.  Thanks again.
 lbass (TechnicalUser) 7 Mar 12 21:53
 I think you should set up each criterion in its own formula like this://{@5987}:if {TABLE.ID} = "5987" and{TABLE.VALUE} = "No" then 1 //{@5986}:If{TABLE.ID} = "5986" and{TABLE.VALUE} in ["No","Unknown"]) then 1//etc.Then insert a group on the patient ID, and write a formula like this for the group section:if maximum({@5986},{table.patientID})+maximum({@5987},{table.patientID})+maximum({@5988},{table.patientID})+maximum({@5989},{table.patientID})=4 then"Meets All Four Criteria"-LB
 LisaRR (TechnicalUser) 8 Mar 12 11:17
 Thank you lbass - that worked perfectly!!!

#### Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!