Multiple aggr in a straight table expression

[#1]

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

[mike_garcia]

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.

[#3]

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

[johnw]

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

[#5]

Thank you both !