The formula below is being used in a pivot table. I think it is the main reason for 'exceeded allocated memory' and 'object out of memory'.
I want to implement this expression in the script. vR1 and vStoreRole are the variables being used in the formula.
SignFlag gives 0 if it is null and 1 if it is not
Table used for Role and RoleID. Multiple 'Role' are selected from the listbox using checkboxes to be used in the formula
Role RoleID
Manager 1
Specialist 2
Partner 3
Staff 4
= If(ISNULL
(Num(Aggr(Count({<RoleID = {"$(vR1)","$(vR2)","$(vR3)","$(vR4)"}>} Distinct SubphaseReference), EngagementKey)
/
Aggr(Count({$<SignoffFlag -={""}>} Distinct SubphaseReference),EngagementKey) *100,0) )
=-1, 0,
Num(Aggr(Count({<RoleID = {"$(vR1)","$(vR2)","$(vR3)","$(vR4)"}>} Distinct SubphaseReference), EngagementKey)
/
Aggr(Count({$<SignFlag -={""}>} Distinct SubphaseReference),EngagementKey)
*100,0)
)
vR1=SubField(vStoreRole,',',1)
vR2=SubField(vStoreRole,',',2)
vR3=SubField(vStoreRole,',',3)
vR4=SubField(vStoreRole,',',4)
vStoreRole=Concat(Distinct RoleID,',')
