Fill data with previous period values when current data is null

[Former Member]

Dear experts,

Models/applications details:

SAP BusinessObjects EPM, Add-in for Microsoft Office

Version 10.0 SP 25 Patch2 .NET4

CPMBPC Release 800, SP-Level 0021 Support package SAPK-80021INCPMBPC

Component: SAP_BW

Release 740

SP-Level : 0020

Support package SAPKW74020

List of dimensions

ACCOUNT_PL (Accounts), TIME, VERSION (Actual / Plan / Forecast), COMPANY, DATASOURCE, FOCUS (Competence center / Business line), MEASURE, RPTCURRENCY, TERM (Lifetime of the contract), PARTNER (Client segment), VENDOR (Supplier).

Script: to be launched by DM package

Calculation engine JAVASCRIPT.

What I am trying to do:

When the dataset already contains some value, the script should leave this value, if the dataset is null, script should write the value from the previous period.

*SELECT(%FIRST_PLAN_PERIOD%,[PLANNING_PERIOD],VERSION,[ID] = %VERSION_SET%) 
*SELECT(%FIRST_PLAN_PERIOD_TIMEID%,[TIMEID],TIME,[ID] = %FIRST_PLAN_PERIOD%)
*SELECT(%TIDL%,[TIMEID],TIME,TIMEID<%FIRST_PLAN_PERIOD_TIMEID%) 
*SELECT(%LAST_ACTUAL_MONTH%,[ID],TIME,TIMEID>= %TIDL% AND TIMEID<%FIRST_PLAN_PERIOD_TIMEID%)
*SELECT(%Y%,[YEAR],TIME,ID = %FIRST_PLAN_PERIOD%) 
*SELECT(%TIDDEC%,[TIMEID],TIME,YEAR = %Y% AND MONTHNUM = 12 AND CALC = N) 
*SELECT(%TIDG%,[TIMEID],TIME,TIMEID>%TIDDEC%)
*SELECT(%NEXTYEAR%,[YEAR],TIME,TIMEID>%TIDDEC% AND TIMEID<= %TIDG%)
*SELECT(%TIDDEC1%,[TIMEID],TIME,YEAR = %NEXTYEAR% AND MONTHNUM = 12 AND CALC = N) 
*SELECT(%TIDG1%,[TIMEID],TIME,TIMEID>%TIDDEC1%) 
*SELECT(%NEXT2YEAR%,[YEAR],TIME,TIMEID>%TIDDEC1% AND TIMEID<= %TIDG1%)
*SELECT(%TIDDEC2%,[TIMEID],TIME,YEAR = %NEXT2YEAR% AND MONTHNUM = 12 AND CALC = N) 
*SELECT(%TIDG2%,[TIMEID],TIME,TIMEID>%TIDDEC2%) 
*SELECT(%NEXT3YEAR%,[YEAR],TIME,TIMEID>%TIDDEC2% AND TIMEID<=%TIDG2%)
*SELECT(%TIDDEC3%,[TIMEID],TIME,YEAR = %NEXT3YEAR% AND MONTHNUM = 12 AND CALC = N) 
*SELECT(%TIDG3%,[TIMEID],TIME,TIMEID>%TIDDEC3%) 
*SELECT(%NEXT4YEAR%,[YEAR],TIME,TIMEID>%TIDDEC3% AND TIMEID<=%TIDG3%)
*SELECT(%LAST_PLAN_PERIOD_TIMEID%,[TIMEID],TIME,[YEAR] = %NEXT4YEAR% AND MONTHNUM = 12)
*SELECT (%HOR%,[ID],TIME,TIMEID>=%FIRST_PLAN_PERIOD_TIMEID% AND ID<=%NEXT4YEAR%.12 AND CALC = N)

*XDIM_MEMBERSET VERSION = VE_FORECAST
*XDIM_MEMBERSET COMPANY=CO_0530006
*XDIM_MEMBERSET TIME=%LAST_ACTUAL_MONTH%,%HOR%
*XDIM_MEMBERSET ACCOUNT_PL=AC_ZRT00543
*XDIM_MEMBERSET FOCUS = BAS(FO_FV)
*XDIM_MEMBERSET PARTNER = PA_P1, PA_P2, PA_P3, PA_P4, PA_P5, PA_P10
*XDIM_MEMBERSET TERM = TE_1, TE_2, TE_3, TE_4, TE_5, TE_6
*XDIM_MEMBERSET VENDOR =VN_1
*XDIM_MEMBERSET DATASOURCE=DS_MANUAL
*WHEN ACCOUNT_PL
*IS AC_ZRT00543
*FOR %T%=%HOR%
*REC(EXPRESSION = ((%VALUE% > 0 ? %VALUE% : [TIME].[TMVL(-1,%T%)])),ACCOUNT_PL = AC_ZRT00543)
*NEXT
*ENDWHEN

Could you please assist?

[former_member186338]

"When the dataset already contains some value, the script should leave this value, if the dataset is null, script should write the value from the previous period." - to my mind the idea is completely wrong....

You will be unable to run the script more then once!

[Former Member]

Could you please suggest another solution?

[former_member186338]

a.tkachova

For sure I can, but you have to explain the business case in details!!!

"When the dataset already contains some value, the script should leave this value, if the dataset is null, script should write the value from the previous period." - this is not a business case description.