topic Multiple aggr in a straight table expression in QlikView

Multiple aggr in a straight table expression

Mon, 05 Jul 2010 23:27:12 GMT

AGGR(NODISTINCT sum({<Month={$(=$(Periode)-1)}>}EFFECTIF),CODE_ACL) +

AGGR(NODISTINCT sum({<Month={$(Periode))}>}EFFECTIF),CODE_ACL)

In a straight table with CODE_ACL as unique dimension, this expression only delivers a result for CODE_ACL having a numeric value in first and second AGGR. If one of the AGGR has a NULL or 0 result, the expression becomes NULL even if the other AGGR result is a number.

Is there anybody to explain this ?

Regards

Olivier

Multiple aggr in a straight table expression

mike_garcia — Mon, 05 Jul 2010 23:44:24 GMT

Don't know whay that happens, but apparently you can not sum a number and a null value. When that happens to me, what I do is force the null value to become a number, like this:

RangeMax(Expr1, 0) + RangeMax(Expr2,0)

That way, whenever the Expr1 or Expr2 returns null, it will be taken as 0.

Hope this helps.

Mike.

Multiple aggr in a straight table expression

Mon, 05 Jul 2010 23:51:18 GMT

Thank you,

I dealt the problem with an if statement testing if my expr was >0. In case of false the expr value was converted to 0. Your solution is better as it shortens the expression.

Regards

Olivier

Multiple aggr in a straight table expression

johnw — Wed, 07 Jul 2010 00:43:26 GMT

Correct - you cannot use a plus sign to add a number and a null value (the result is null). However, you CAN use rangesum(), and all null values in the sum will be treated as 0. So I suspect that this will work:

RANGESUM(AGGR(NODISTINCT sum({<Month={$(=$(Periode)-1)}>}EFFECTIF),CODE_ACL)
,AGGR(NODISTINCT sum({<Month={$(Periode))}>} EFFECTIF),CODE_ACL))

Multiple aggr in a straight table expression

Thu, 08 Jul 2010 08:27:38 GMT

Thank you both !