Running Total Based on Multiple Formulas 1

[LisaRR]

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]

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]

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.VALUE
5987 No
5987 Yes
5986 Unknown
5986 Yes
5988 No contraidications
5988 Allergic to eggs
5989 No exclusions

Does this provide enough additional information? Thanks so much for you assistance.

 

[kray4660]

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]

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 true
else false

 

[LisaRR]

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]

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]

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]

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]

Yes, that's right. I need to evaluate all the records for the condition to determine vaccine eligibility. Thanks again.

 

[lbass]

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]

Thank you lbass - that worked perfectly!!!