AGGR function returns unexpected Null (Zero) values
Article Number: 000044483 | Last Modified: 2018/11/17
Description
This behavior is working as designed. In some cases you might encounter that for certain aggregation calculation the AGGR function (in combination with SUM and COUNT functions) returns a Null. In most cases, however, this will not disturb the result and is intended to speed up the calculation performance in the background.
Resolution
The Qlik Sense engine cycles through every Table in the Datamodel and generates a hidden Null to improve calculation time and indexing when the AGGR function is used. The behavior can be for example observed when using the following expression with the test (Ctrl+qq) Script:
concat(aggr(sum(Expression1), Dim1),',') or concat(aggr(count(Expression1), Dim1),',')
which may result as 0,138737,25602,92200
Notice the Null in the beginning. As mentioned this should normally not effect any normal aggregation calculation. If for some reason the null value should not be returned at all then a set expression can be used as a work around like:
concat(aggr(sum({$<Dim1={'*'}>}Expression1), Dim1),',') or concat(aggr(count({$<Dim1={'*'}>}Expression1), Dim1),',')
The result now will be: 138737,25602,92200 , without the leading Zero.
Another work around might be to un check "include null values" in the object properties for the respective data.
Get Answers
Find Answers
Qlik Community
Collaborate with over 60,000 Qlik technologists and members around the world to get answers to your questions, and maximize success.
Experiencing a serious issue, please contact us by phone. For Data Integration related issues please refer to your onboarding documentation for current phone number.