4 Replies Latest reply on Jul 28, 2016 2:02 PM by Ryan Jablon

    Logical Statements: >,<,=, +-

    Ryan Jablon

      I am trying to write a calculated field that says

       

      "

      IF [Demand Week] < INT([Build Week]) Then 'Late'

      ELSEIf [Demand Week] = INT([Build Week]) Then 'On time'

      ElseIf [Build Week] = "Not slotted" Then 'TBA'

      Else 'Early'

      END

       

      '

      However I would like On time to include +- 3 so that things 3 weeks late or 3 weeks early are still considered On time. Not sure the correct syntax for this though.

        • 1. Re: Logical Statements: >,<,=, +-
          Peter Halpin

          I haven't tested this myself but I think something like this may work.

           

          IF [Demand Week] < (INT([Build Week]) + 3) THEN 'Late'

          ELSEIF ABS([Demand Week] - INT([Build Week])) <= 3 THEN 'On time'

          ELSEIF [Build Week] = "Not slotted" THEN 'TBA'

          ELSE 'Early'

          END

          • 2. Re: Logical Statements: >,<,=, +-
            Kathryn Bridges

            I think what you'll want to do is similar to below:

             

            IF abs(datediff('week',demand week,build week))<=3 then "On time"

            elseif  (datediff('week',demand week,build week))>3 then "Late"

            elseif (datediff('week',demand week,build week))<-3 then "Early"

            elseIf [build week] = "Not slotted" Then 'TBA'

            end.

             

            If your weeks aren't truly dates, you should be able to just use the difference between the weeks,

             

            Hope this helps.

             

            Thanks!

            • 3. Re: Logical Statements: >,<,=, +-
              Derek Wong

              Ryan,

               

              Could you use the DateAdd function to add a week for each exception you want to add? You may have to tweak the actual logic of what dates you're adding (you may want to swap and add demand week?), but this method should work in theory.

               

              IF DATEADD('week', 1, [Demand Week]) = INT([Build Week]) Then 'On time'

              ELSEIf DATEADD('week', 2, [Demand Week])= INT([Build Week]) Then 'On time'

              ELSEIf DATEADD('week', 3, [Demand Week])= INT([Build Week]) Then 'On time'

              ELSEIf DATEADD('week', -1, [Demand Week]) = INT([Build Week]) Then 'On time'

              ELSEIf DATEADD('week', -2, [Demand Week])= INT([Build Week]) Then 'On time'

              ELSEIf DATEADD('week', -3, [Demand Week])= INT([Build Week]) Then 'On time'

               

              ELSEIF [Demand Week] < INT([Build Week]) Then 'Late' //notice I have moved this to after the "on time" section, so that those exceptions will run first

               

              ElseIf [Build Week] = "Not slotted" Then 'TBA'

              Else 'Early'

              END

              • 4. Re: Logical Statements: >,<,=, +-
                Ryan Jablon

                Perfect, that worked. Thank you for your help Peter.