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

    IF & AND Criteria

    Benjamin Rubattel



      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"



      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




        • 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"