Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I ended up adding another script to get the GRCA
Load
GRCA,
sum (Var_HKD000) as GRCAVAR
resident [New] Group by GRCA;
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.
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.
Have you tried Sum(All <GRCA> Var HKD0000)?
Thanks a lot for your immediate guidance. I have tried and the number is wrong.
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.
I ended up adding another script to get the GRCA
Load
GRCA,
sum (Var_HKD000) as GRCAVAR
resident [New] Group by GRCA;