The above [Ip Lcl Ref Cde Ith] is customer ID. What I was trying to do is to only provide in the pivot table Top 50 customer by absolute variance, and the customer id appears in both on balance sheet and off balance sheet acct code.
In my Pivot table below, I also have 2 complicated Aggr
Below relates to field [ChkVar(>10B/10%)].I try to have alert when a customer has abs variance>10bn or 10% of GRCA:-
=aggr((if((fabs(sum(t1_amt_HKD000)-sum(t0_amt_HKD000)))>=100000 or fabs(sum(Var_HKD000)/Sum(GRCAVAR)))>=0.1 ,'>10B/10%',''),[Ip Lcl Ref Cde Ith],GRCA,CRRcheck,StageChk)
Below Calculated Dimension relates to [Total Check] , I try to have count no. of 3 alerts if CRRcheck is not blank, if StageChk is not blank and absolute Var >10bn