Hello,
I am using below rule to calculate 13 month average of an account for about 53 accounts and we have about 60 Profit centers in C1 and 300 entities. So currently it increased my consolidation time by 45 mins.
Can anyone please advise how to optimize below rule or if you have any other logics to calculate 13 month average for an account.
Thank you!
AA
Sub Calculate
v13MAvgPAccList = .Account.List("","13MAvg") '13MAvg calculation for Parent Accounts
For Each v13MAvgPAcc in v13MAvgPAccList
.Exp "A#" & v13MAvgPAcc & ".ICP#[ICP None].C2#Endbal.C3#13MAvg.C4#[None].C1#" & PC & " = " & Average("A#" & v13MAvgPAcc & ".C1#" & PC, "13")
Next
End Sub
' programming of the AVERAGE function
FUNCTION Average(strPOV,strPERIOD)
DIM nPERIOD
DIM strCUM
DIM i
nPERIOD = CINT(strPERIOD)
FOR i = 0 TO nPERIOD-1
IF i = 0 THEN
strCUM = strPOV &".P#CUR" & ICPC2C3C4Top
ELSE
strCUM = strCUM &"+"& strPOV &".P#CUR-"&i & ICPC2C3C4Top
END IF
NEXT
Average = "(("& strCUM &")/"& nPERIOD &")"
END FUNCTION