topic Re: Incorrect expression in QlikView

Incorrect expression

Wed, 18 Jun 2014 06:01:05 GMT

Hello guys,

The following is a pivot table chart type. If you notice, the Days column summation is incorrect (in which is should be 31 + 31 + 422 = 468). The following is the expression I'm using for the Days column.

IF(InvUOMCode='MTH' OR InvUOMCode='EA' OR InvUOMCode='LT' OR InvUOMCode='SET' OR ISNULL(InvUOMCode) or InvUOMCode=''
,SUM(InvQuantity)
,(DAY(MONTHEND(MAKEDATE(YEAR(CDate),MONTH(CDate))))))

Can somebody help me to point out where I done did wrong on my expression?

Thank you very much for your kind attention guys.

Re: Incorrect expression

tresB — Wed, 18 Jun 2014 06:08:34 GMT

Re-write expression using aggr() like:

=Aggr(Sum(IF(InvUOMCode='MTH' OR InvUOMCode='EA' OR InvUOMCode='LT' OR InvUOMCode='SET' OR ISNULL(InvUOMCode) or InvUOMCode=''
,SUM(InvQuantity)
,(DAY(MONTHEND(MAKEDATE(YEAR(CDate),MONTH(CDate)))))) ), Country, [Fixed Asset Code], [Project Code], [Invoice Date], [InvoiceNo/...] , UOM)

Note: replace '[InvoiceNo/...]' with proper field name.

Re: Incorrect expression

Anonymous — Wed, 18 Jun 2014 06:21:49 GMT

Try below,

IF(InvUOMCode='MTH' OR InvUOMCode='EA' OR InvUOMCode='LT' OR InvUOMCode='SET' ORISNULL(InvUOMCode) or InvUOMCode=''
,Aggr(SUM(InvQuantity), Country, [Fixed Asset Code], [Project Code], [Invoice Date], [Invoice No], UOM)
,(DAY(MONTHEND(MAKEDATE(YEAR(CDate),MONTH(CDate))))))