Expression Query

[joanneandrews]

Good day all,

I am new to Qlikview and need your expert help please.

I need to write an expression that looks at the sales order (and if duplicate then to only assign "1" to the sales order, then to add up the average cost per unit by the no of loads. Currently when writing this expression:

Sum([Avg Cost Per Unit])/Sum(TOTAL [No of Loads])

the problem is that the "No of Loads" is adding up to 4 (in the below data set example) where in fact it is one load just different products on the same order. 

Can this be done with an expression or do i need to do this manually for the thousands of line items in my data set?

Example Data Set:

Thank you for your help in advance.

Joanne

[tresesco · Accepted solution]

You could probably use DISTINCT qualifier in the expression, like:

Sum([Avg Cost Per Unit])/Sum(Distinct TOTAL [No of Loads])