Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Not applicable

Multiple aggr in a straight table expression

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



4 Replies
Highlighted
Valued Contributor

Multiple aggr in a straight table expression

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.

Miguel García
Qlik Expert, Author and Trainer
Highlighted
Not applicable

Multiple aggr in a straight table expression

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

Highlighted
MVP
MVP

Multiple aggr in a straight table expression

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

Highlighted
Not applicable

Multiple aggr in a straight table expression

Thank you both !