12 Replies Latest reply on Feb 10, 2019 10:37 AM by Lisa Geller

# "And"  in If/Then calculation

I am trying to set up an If/Then calculation  that has 2 parts.  For the first part to be true, it has to satisfy 2 conditions and for the 2nd part to be true it only needs to satisfy one condition.  The calculation is as follows:

IF

DATEDIFF('minute', [Severe Sepsis Present Dtm], [Antibiotic Admin Dtm])<= 180
AND [Antibiotic Admin Selection] = 1

THEN 1

ELSEIF

(DATEDIFF('minute', [Antibiotic Admin Dtm], [Severe Sepsis Present Dtm]) <=1440)

THEN 1

ELSE 0 END

The problem is that the "and" statement in the first part of the equation is not being looked at.  I can set the value of the [Antibiotic Admin Selection] to anything - I can even  take it out of the equation entirely - and it has no effect on my results.  I know that the AND function will short circuit the calculation if the first part of the calculation is true but I don't understand how to make a calculation that will satisfy 2 conditions then.  In my data the [antibiotic admin selection[ field can either be blank 1, or 2.  I need it to be "1" and I need it to be only looked at if the first part of my calculation is true.  I'm sure there is a very easy solution to this but I cannot seem to figure it out.

I tried putting the {antibiotic admin selection] before the datediff equation but it still didn't work.

Here's a screenshot.

Thanks for any help,

Lisa

• ###### 1. Re: "And"  in If/Then calculation

So, just to clarify, you want the calculated field to return 1 if either of the following are true:

1. Antibiotic Admin Dtm is between 0 and 180 days after Severe Sepsis Present Dtm and Antibiotic Admin Selection = 1
2. Severe Sepsis Present Dtm is between 0 and 1440 days after Antibiotic Admin Dtm

Is this logic correct?

If so, then your calculated field should work as intended and we'd probably need to see a sample workbook in order to troubleshoot further.

If not, can you explain the logic that you need to use in the calculated field?

• ###### 2. Re: "And"  in If/Then calculation

An AND condition would only short circuit if the first condition is FALSE. If it's true, it still needs to check the 2nd condition. Without a workbook and insight into your data, it's hard to say.

But if I was debugging, I would duplicate the calculation 3 times, one for each condition. Then remove other code from each calc so I could test each condition separately in a cross-tab, and see what you are getting for each. Then piece them back together after you find your error.

2 of 2 people found this helpful
• ###### 3. Re: "And"  in If/Then calculation

+1 This is a great approach for debugging calculated fields!

1 of 1 people found this helpful
• ###### 4. Re: "And"  in If/Then calculation

I want the logic to be true (or 1) if:

If antibiotic datetime is between 0-180 minutes after severe sepsis time AND antibiotic selection =1

Or

If antibiotic datetime is between 0-1440 minutes before severe sepsis time

Does that make sense?

(I reversed the order of the antibiotic and severe sepsis dates and times on the second part of the calc)

• ###### 5. Re: "And"  in If/Then calculation

Your logic looks right to me. This is what I'd write based on your requirements, and it looks just like yours. So I would assume there's a something unexpected in your data. Or i'm making the same mistake as you

You can double check your dates are proper with full times and aren't defaulting to an empty time, like  2/9/2019 - 0000hrs.

IF

(DATEDIFF('minute', [Severe Sepsis Present Dtm], [Antibiotic Admin Dtm])<= 180
AND [Antibiotic Admin Selection] = 1)

OR

(DATEDIFF('minute', [Antibiotic Admin Dtm], [Severe Sepsis Present Dtm]) <=1440)

THEN 1

ELSE 0 END

• ###### 6. Re: "And"  in If/Then calculation

Your calculated field looks right. Any chance of seeing a workbook?

• ###### 7. Re: "And"  in If/Then calculation

I'm attaching a packaged workbook.

I know the numbers I should be getting - Q2 should have 60 1's and 5 0's and Q3 should have 68 1's and 2 0's..  In the excel file that's attached I highlighted in red the cases that my calculation is missing.

Thanks for taking a look because I am stumped.

• ###### 8. Re: "And"  in If/Then calculation

I was able to get Q2, i don't Know Why Q3 is not coming up

Thanks

Deepak

• ###### 9. Re: "And"  in If/Then calculation

Here's what finally worked.  I used the Antibiotic Admin Column instead of using the times.

IF ([Antibiotic Admin] = 1 AND  [Antibiotic Admin Dtm] >= [Severe Sepsis Present Dtm])
AND [Antibiotic Admin Selection] = 1

OR
THEN 1 ELSE 0 END

I have no idea why our above equation did not work but I'm happy I got it worked out.

Thanks for the help everyone.

• ###### 10. Re: "And"  in If/Then calculation

Here's what I worked out. The negative numbers are "before" times. I kept it that way to keep the calculation easier to read.

TWBX attached. Sheet 2 is for testing the conditions, sheet 3 is using my calculation. Based on my test (Screenshot 1), the logic of the my calculation (Screenshot 2) seems right. I think your original was failing because you weren't checking between (0-180) minutes , or  (-1400 - 0) minutes.

Final Calculation

1 of 1 people found this helpful
• ###### 11. Re: "And"  in If/Then calculation

Great you got it , atleast you may have marked The replies Helpful for Everyone who spent time on this as this was not easy to understand and crack.

Thanks

Deepak

• ###### 12. Re: "And"  in If/Then calculation

That's exactly it Nick!!  I unmarked my solution as correct and marked yours since, although either one gives you the same answer, I was trying to figure it out using time stamps and that how you solved it.  I had a feeling I needed to break those date diff equations up into their component parts but could quite manage to figure out how to do it.  I think using the negative number was the key because it was very easy to understand that way.

Thanks so much!!  I appreciate all the help everyone.