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

# Expression Help Needed... badly

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

• ###### Re: Expression Help Needed... badly

May be this:

Aggr(If(SubStringCount(Concat(DISTINCT LIC, '|'), 'Non-Compliant') = 1, 'Non-Compliant, 'Compliant'), [Purchase Order])

• ###### Re: Expression Help Needed... badly

In fact, if you have Purchase Order as your dimension, you might not even need to add the Aggr() function. Try like this:

If(SubStringCount(Concat(DISTINCT LIC, '|'), 'Non-Compliant') = 1, 'Non-Compliant, 'Compliant')

• ###### Re: Expression Help Needed... badly

Or maybe just like

=MaxString(LIC)

• ###### Re: Expression Help Needed... badly

Thank you so much for your responses!

So here is what I have tried.

Aggr(If(SubStringCount(Concat(DISTINCT LIC, '|'), 'Non-Compliant') = 1, 'Non-Compliant, 'Compliant'), [Purchase Order]) did not work. All of the data disappeared when I clicked "Apply".

I am using the PO as a dimension so I tried If(SubStringCount(Concat(DISTINCT LIC, '|'), 'Non-Compliant') = 1, 'Non-Compliant, 'Compliant') and that yielded the same result that I was getting when I used

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

=MaxString(LIC) ... I could not get this one to work either. The data disappeared again.

• ###### Re: Expression Help Needed... badly

Do you have any other dimension except the PO dimension in your chart?

• ###### Re: Expression Help Needed... badly

LIC is not a field, but an expression reference? How does this expression look like and which dimensions are you using (AKA please post the complete description of your setting / context)?

• ###### Re: Expression Help Needed... badly

I right click on the text table that I have built so far and click on properties. In the "Used Dimensions" field on the "Dimensions" tab, I have the following: Purchase Order, PO: Created By, Approver, PO Creation Date, Invoice Date, Invoice Date - PO Date, PO Line Item Compliance (this was calculated in Excel), Overall PO Compliance (this was also calculated in Excel), and LIC (LIC is a calculated column within the script).

I included PO Line Item Compliance and Overall PO Compliance so I could check my script with the results from Excel. Once I get the formula to work, I will be removing both of these columns from the model.

• ###### Re: Expression Help Needed... badly

I've just noticed that you've posted this on QV Expressor place.

If you need the expression to work in expressor, I don't think I can be of any help here.

• ###### Re: Expression Help Needed... badly

Ditto

• ###### Re: Expression Help Needed... badly

Thank you all soooo much! With your help, I figured it out!

Here is what ended up working:

= IF(SubStringCount(Concat(Distinct LIC, '|'), 'Non-Compliant') <= 1, 'Non-Compliant', 'Compliant')

• ###### Re: Expression Help Needed... badly

Hm, wouldn't this expression return 'Compliant' if more than 1 LIC shows 'Non-Compliant'?