
1. Re: "And" in If/Then calculation
Ken Flerlage Feb 9, 2019 3:49 PM (in response to Lisa Geller)So, just to clarify, you want the calculated field to return 1 if either of the following are true:
 Antibiotic Admin Dtm is between 0 and 180 days after Severe Sepsis Present Dtm and Antibiotic Admin Selection = 1
 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
Nick Parsons Feb 9, 2019 3:54 PM (in response to Lisa Geller)2 of 2 people found this helpfulAn 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 crosstab, and see what you are getting for each. Then piece them back together after you find your error.

3. Re: "And" in If/Then calculation
Ken Flerlage Feb 9, 2019 3:56 PM (in response to Nick Parsons)1 of 1 people found this helpful+1 This is a great approach for debugging calculated fields!

4. Re: "And" in If/Then calculation
Lisa Geller Feb 9, 2019 4:08 PM (in response to Ken Flerlage)I want the logic to be true (or 1) if:
If antibiotic datetime is between 0180 minutes after severe sepsis time AND antibiotic selection =1
Or
If antibiotic datetime is between 01440 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
Nick Parsons Feb 9, 2019 4:27 PM (in response to Lisa Geller)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
Ken Flerlage Feb 9, 2019 5:52 PM (in response to Lisa Geller)Your calculated field looks right. Any chance of seeing a workbook?

7. Re: "And" in If/Then calculation
Lisa Geller Feb 9, 2019 6:34 PM (in response to Lisa Geller)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.

abx.twbx 21.4 KB


8. Re: "And" in If/Then calculation
Deepak Rai Feb 9, 2019 7:02 PM (in response to Lisa Geller)
abx_v10.5.twbx 56.7 KB


9. Re: "And" in If/Then calculation
Lisa Geller Feb 10, 2019 5:20 AM (in response to Lisa Geller)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] = 1OR
([Antibiotic Admin] = 1 AND [Antibiotic Admin Dtm]<=[Severe Sepsis Present Dtm])
THEN 1 ELSE 0 ENDI 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
Nick Parsons Feb 10, 2019 6:04 AM (in response to Lisa Geller)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 (0180) minutes , or (1400  0) minutes.
Final Calculation

abx.twbx 158.8 KB


11. Re: "And" in If/Then calculation
Deepak Rai Feb 10, 2019 8:51 AM (in response to Lisa Geller)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
Lisa Geller Feb 10, 2019 10:37 AM (in response to Nick Parsons)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.