topic Re: Sum(Aggr((Sum()) returns null in App Development

Sum(Aggr((Sum()) returns null

43918084 — Mon, 20 Dec 2021 21:02:46 GMT

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.

Re: Sum(Aggr((Sum()) returns null

zwilson_borg — Sun, 28 Mar 2021 03:31:47 GMT

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.

Re: Sum(Aggr((Sum()) returns null

43918084 — Sun, 28 Mar 2021 03:39:22 GMT

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.

Re: Sum(Aggr((Sum()) returns null

zwilson_borg — Sun, 28 Mar 2021 03:41:18 GMT

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

Re: Sum(Aggr((Sum()) returns null

43918084 — Sun, 28 Mar 2021 03:48:47 GMT

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

Re: Sum(Aggr((Sum()) returns null

43918084 — Sun, 28 Mar 2021 03:50:03 GMT

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.

Re: Sum(Aggr((Sum()) returns null

43918084 — Sun, 28 Mar 2021 13:24:57 GMT

I ended up adding another script to get the GRCA

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