1 Reply Latest reply on Oct 24, 2017 5:34 AM by Neil Lord

    IF & AND Criteria

    Benjamin Rubattel

      Hello,

       

      Hello, I am looking to calculate the OTD on the Line orders for sales documents.

       

      • For each lines I have created an expected Good Issue Date based on the line Creation Date
      • If the Good Issue has been made, there is an actual good issue date
      • If the good issue has not been made, there will be a "NULL" in the field (link between two tables)
      • I add a field Today () to know the today date

       

      I would like to:

       

      Actual Good Issue = NULL: If the expected good issue date => than TODAY then "Open - On Schedule" else "Open - late"

      Actual Good Issue = DATE: IF the actual good issue date =< than EXPECTED GOOD ISSUE DATE then "On time" Else "Late"

      END

       

      Actual Good Issue             Expected Good Issue                       Date Today                       Output

      NULL                                   24.10.2017                                   24.10.2017                   Open - On Schedule

      NULL                                   22.10.2017                                   24.10.2017                   Open - Late

      23.10.2017                          21.10.2017                                   24.10.2017                   Late

      25.08.2017                          10.10.2017                                   24.10.2017                   On time

       

       

      thanks for your help

      Benjamin

       

       

        • 1. Re: IF & AND Criteria
          Neil Lord

          Hi Benjamin

           

          This should work:

           

          If IsNull([Actual Good Issue]) AND [Expected Good Issue]>= Today() Then "Open - On Schedule"

          ElseIF IsNull([Actual Good Issue]) AND [Expected Good Issue]< Today() Then "Open - Late"

          ElseIf Not IsNull([Actual Good Issue]) and [Actual Good Issue]<= [Expected Good Issue] Then "On Time"

          Else "Late"

           

          End