Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
riceball
Contributor
Contributor

Help on Aggr/Sum for UnitPrice*Qty

Hi,

I've been having problem in getting the correct variance value as shown in the screenshot. It was calculated based on "([A.ShipQty_ASN] - [ReceiptQty_SR])*(B.Unit Price_ASN). In other word, It's (A-C)*B. The figure ($347) was correctly shown in the second table.

But when I tried to remove the field for "PO Number and Item Number", the Variance 1 figures in Table 1 seems to sum up the total Unit price instead of getting the correct value of $347.

Appreciate your help on this.

1 Solution

Accepted Solutions
Not applicable

HI,

In the first chart try using the below expression:

sum(aggr(sum(([A.ShipQty_ASN] - [ReceiptQty_SR])*(B.Unit Price_ASN)),PONumber_PO,ItemNumberD_PO))

Kiran.

View solution in original post

7 Replies
Not applicable

HI,

In the first chart try using the below expression:

sum(aggr(sum(([A.ShipQty_ASN] - [ReceiptQty_SR])*(B.Unit Price_ASN)),PONumber_PO,ItemNumberD_PO))

Kiran.

riceball
Contributor
Contributor
Author

Hi Kiran,

That's cool! It works!!!

Thank you so much for your help!!!!

riceball
Contributor
Contributor
Author

Hi,

Can the function be used with IF/Set Analysis function? I tried to use the following IF expressions but it cant work.

IF((ConfirmMM_SR>StatementMMD OR NULLCOUNT(ConfirmMM_SR)) OR ConfirmYYYY_SR>StatementYYYD,

(Sum(Aggr(Sum(([QtyShpt_ASN]-[ConfirmQTY_SR])*(UnitPrice_ASN)),PONumber_PO,ItemNumberD_PO))))

OR

SUM(Aggr(IF((ConfirmMM_SR>StatementMMD OR NullCount(ConfirmMM_SR)) OR ConfirmYYYY_SR>StatementYYYYD, Sum(([QtyShpt_ASN]-[ConfirmQTY_SR])*(UnitPrice_ASN)),PONumber_PO,ItemNumberD_PO))

The result for the first table would end up as attached. The correct answer should be 353,970 as shown in the second table

Screenshot 2.bmp

Would appreciate your help on this.

Thanks.

Not applicable

Can you try this expression:

SUM(Aggr(Sum(if(ConfirmMM_SR>StatementMMD OR NullCount(ConfirmMM_SR) OR ConfirmYYYY_SR>StatementYYYYD

,([QtyShpt_ASN]-[ConfirmQTY_SR])*(UnitPrice_ASN),0)),PONumber_PO,ItemNumberD_PO))

Kiran Rokkam.

riceball
Contributor
Contributor
Author

Hi,

Thanks for your help. However, the mentioned expression will give me the result of 0 instead

Not applicable

Which means there is no data with the given condition and zero is returned. I am not sure of the intention for NullCount() function but this might have a problem as it seems to count only if it is null. NullCount() is a group function and it doenst make sense at a row level conditions.

Kiran.

riceball
Contributor
Contributor
Author

Hi Kiran,

Thanks for pointing out my mistake!! My intention is to pull out those data that doesn't have 'ConfirmMM_SR' record (i.e. ConfirmMM_SR= NULL).

Your are right, NullCount() function does not work in this case, so I try to modify my initial expression by replacing NullCount() function with ISNULL() Function and it works!

IF((ConfirmMM_SR>StatementMMD OR ISNULL(ConfirmMM_SR)) OR ConfirmYYYY_SR>StatementYYYYD,

Sum(aggr(sum(([QtyShpt_ASN] - [ConfirmQty_SR])*(UnitPrice_ASN)),PONumber_PO,ItemNumberD_PO)))

Again, thanks for your help!! =D