Help on Aggr/Sum for UnitPrice*Qty

[riceball]

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.

[#2 · Accepted solution]

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]

Hi Kiran,

That's cool! It works!!!

Thank you so much for your help!!!!

[riceball]

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

Would appreciate your help on this.

Thanks.

[#5]

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]

Hi,

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

[#7]

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]

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