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 of 2 people found this helpful
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.
1 of 1 people found this helpful
+1 This is a great approach for debugging calculated fields!
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
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)
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.
(DATEDIFF('minute', [Severe Sepsis Present Dtm], [Antibiotic Admin Dtm])<= 180
AND [Antibiotic Admin Selection] = 1)
(DATEDIFF('minute', [Antibiotic Admin Dtm], [Severe Sepsis Present Dtm]) <=1440)
ELSE 0 END
Your calculated field looks right. Any chance of seeing a workbook?
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
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
([Antibiotic Admin] = 1 AND [Antibiotic Admin Dtm]<=[Severe Sepsis Present Dtm])
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.
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.
abx.twbx 158.8 KB
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.
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.