Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
43918084
Creator II
Creator II

Sum(Aggr((Sum()) returns null

I have below Sum(Aggr((Sum()) aiming to return total of each category within a field [GRCA]

sum(aggr((Sum(t1_amt_HKD000)-sum(t0_amt_HKD000)),GRCA))

But some of the field returns null.  Would appreciate some guidance to resolve.  Many thanks.

Capture.PNG

Labels (1)
1 Solution

Accepted Solutions
43918084
Creator II
Creator II
Author

 I ended up adding another script to get the GRCA

Load
GRCA,
sum (Var_HKD000) as GRCAVAR
resident [New] Group by GRCA;

View solution in original post

6 Replies
zwilson_borg
Contributor III
Contributor III

Because each of the other reference fields is not included in the Aggr grouping parameters, it is not aggregating with reference to them. This can create extremely inconsistent results. Make sure to include all the relevant dimensions in the aggr statement, you can include more than one.

43918084
Creator II
Creator II
Author

Thank you very much.  But if I include other reference fields, then I will not have the total by GRCA variance.  The GRCA column on the left table will become the same as the 3rd column. This is because my table on the left hand side is only top 50. The table on the right hand side is the GRCA variance by category of whole population.

I have tried using Sum (Total <GRCA> Var HKD000)  but it still does not work.

zwilson_borg
Contributor III
Contributor III

Have you tried Sum(All <GRCA> Var HKD0000)? 

43918084
Creator II
Creator II
Author

Thanks a lot for your immediate guidance.  I have tried and the number is wrong.Capture.PNG

43918084
Creator II
Creator II
Author

If I use sum(aggr((Sum(t1_amt_HKD000)-sum(t0_amt_HKD000)),GRCA))

I could get the correct number in some fields but others are null.

43918084
Creator II
Creator II
Author

 I ended up adding another script to get the GRCA

Load
GRCA,
sum (Var_HKD000) as GRCAVAR
resident [New] Group by GRCA;