topic Re: Help on Aggr/Sum for UnitPrice*Qty in QlikView

Help on Aggr/Sum for UnitPrice*Qty

riceball — Fri, 24 Aug 2012 02:04:19 GMT

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.

Re: Help on Aggr/Sum for UnitPrice*Qty

Fri, 24 Aug 2012 03:09:05 GMT

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.

Re: Help on Aggr/Sum for UnitPrice*Qty

riceball — Fri, 24 Aug 2012 03:57:27 GMT

Hi Kiran,

That's cool! It works!!!

Thank you so much for your help!!!!

Re: Help on Aggr/Sum for UnitPrice*Qty

riceball — Tue, 28 Aug 2012 09:32:45 GMT

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))))

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.

Re: Help on Aggr/Sum for UnitPrice*Qty

Tue, 28 Aug 2012 13:06:33 GMT

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.

Re: Help on Aggr/Sum for UnitPrice*Qty

riceball — Wed, 29 Aug 2012 05:59:59 GMT

Hi,

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

Re: Help on Aggr/Sum for UnitPrice*Qty

Thu, 30 Aug 2012 05:06:54 GMT

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.

Re: Help on Aggr/Sum for UnitPrice*Qty

riceball — Fri, 31 Aug 2012 01:38:35 GMT

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