Skip to Content
0
Jul 23, 2018 at 03:07 PM

TMVL with WHEN_REF_DATA not working

43 Views

I am trying to pull an account (Acct A) from the previous month to calculate a current month's account (Acct B) balance. However I am having trouble when there is no data in the current month for Acct A. Script is as follows. First part is adding accounts in a rollup to one account. then taking that one account from previous month and applying 11%/12 and saving in next month.

*RUNALLOCATION
*FACTOR = 1
*DIM ACCOUNT WHAT=BAS(SVA_CAPITAL_EMPLOYED); WHERE=SVA_CAP_EMP_CALC
*DIM CATEGORY WHAT=%CATEGORY_SET%; WHERE=<<<
*DIM DATASRC WHAT=BAS(ALL_PCA); WHERE=CALC
*DIM KFACCOUNT WHAT=VALUE; WHERE=<<<
*DIM PROFITCENTER WHAT=BAS(ALL); WHERE=<<<
*DIM RPTCURRENCY WHAT=LC,USD; WHERE=<<<
*DIM TIME WHAT=%TIME_SET%; WHERE=<<<
*ENDALLOCATION

*XDIM_MEMBERSET ACCOUNT = SVA_CAP_EMP_CALC
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_MEMBERSET DATASRC = CALC
*XDIM_MEMBERSET KFACCOUNT = VALUE
*XDIM_MEMBERSET PROFITCENTER = BAS(ALL)
*XDIM_MEMBERSET RPTCURRENCY= USD,LC
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET MEASURES = PERIODIC


*WHEN_REF_DATA = MASTER_DATA

*FOR %TIM% = %TIME_SET%
*XDIM_MEMBERSET TIME = %TIM%

*WHEN ACCOUNT
*IS SVA_CAP_EMP_CALC

*WHEN RPTCURRENCY
*IS LC
*REC(EXPRESSION = ((([TIME].[TMVL(-1,%TIM%)],[RPTCURRENCY].[LC],[ACCOUNT].[SVA_CAP_EMP_CALC])*.11/12)),ACCOUNT = ZKFCAPCHRG, DATASRC = CALC, RPTCURRENCY = LC, KFACCOUNT = VALUE)

*IS USD
*REC(EXPRESSION = ((([TIME].[TMVL(-1,%TIM%)],[RPTCURRENCY].[USD],[ACCOUNT].[SVA_CAP_EMP_CALC])*.11/12)),ACCOUNT = ZKFCAPCHRG, DATASRC = CALC, RPTCURRENCY = USD, KFACCOUNT = VALUE)

*ENDWHEN
*ENDWHEN

*NEXT

For example in May I would have

SVA_CAP_EMP_CALC to be 1,000. Then for June I would expect

ZKFCAPCHRG to be 1,000 * .11 / 12 = 9.16

I am getting this number if

SVA_CAP_EMP_CALC has a value for June but not if this account is blank in June.