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

    "And"  in If/Then calculation

    Lisa Geller

      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
          Ken Flerlage

          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
            Nick Parsons

            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
              Ken Flerlage

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

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

                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
                  Nick Parsons

                  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

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

                    • 7. Re: "And"  in If/Then calculation
                      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.

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

                        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
                          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] = 1

                          OR
                          ([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.

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

                            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.

                             

                            Screen Shot 2019-02-10 at 5.51.14 AM.png

                             

                            Final Calculation

                            Screen Shot 2019-02-10 at 5.52.22 AM.png

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

                              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

                                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.