11 Replies Latest reply: May 5, 2016 2:25 PM by Stefan Wühl RSS

    Expression Help Needed... badly

    Jessica Forand

      Hello everyone,

       

      I am trying to create a formula that determines overall Purchase Order Compliance for POs that contain both non-compliant and compliant line items.

       

      PO Non-Compliance Criteria (all 3 conditions must be true for a PO to be marked non-compliant):

      • PO Date > Invoice Date
      • PO Date = Invoice Date
      • Any PO line item date > Invoice Date

       

      In Excel, I used the following formula to determine overall PO compliance:

      =IF(COUNTIFS(U:U,"Non-Compliant",E:E,[@[Purchase Order]])=0,"Compliant", "Non-Compliant")

      where U=Line Item Compliance and E=Purchase Order Number

       

      I need QlikView to look at all of the PO line items for any given Purchase Order # and if any one of it's line items are "Non-Compliant", I need the entire PO number to be marked as "Non-Compliant". If all the line items are "Compliant", I need the entire PO to be marked as "Compliant".

       

      The closest I have gotten was by using the following expression:

      =IF(LIC='Non-Compliant' AND (Count(Distinct([Purchase Order]))),'Non-Compliant','Compliant')

       

       

      LIC = Line Item Compliance