How to calculate next or previous time member in BPC script logic

Traditional approach require the TMVL function. But the result of TMVL can’t be used in *SELECT statements. For example the following code will generate no result:

//%TIME_SET%=2007.04 
*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) //%Y%=2007
*SELECT(%IDDEC%,[ID],TIME,YEAR=%Y% AND MONTHNUM=12 AND CALC=N) //%IDDEC%=2007.12
*SELECT(%NEXTYEAR%,[YEAR],TIME,ID=TMVL(1,%IDDEC%)) //Incorrect syntax
*XDIM_MEMBERSET TIME=%NEXTYEAR%.01
//Result: *XDIM_MEMBERSET TIME=%NEXTYEAR%.01

It’s possible to use SELECT statements to find the next member and store result in some variable.

First requirement – TIMEID property has to be properly maintained in TIME dimension (only for base members, numeric character strings in the increasing order, format can be YYYYMM00):

The following script will calculate the next TIME member related to the member selected in %TIME_SET%:

//%TIME_SET%=2007.04 
*SELECT(%TID%,[TIMEID],TIME,ID=%TIME_SET%) //%TID%=20070400
*SELECT(%TIDG%,[TIMEID],TIME,TIMEID>%TID%) //%TIDG%=20070500,20070600,...
*SELECT(%NEXTPER%,[ID],TIME,TIMEID>%TID% AND TIMEID<=%TIDG%)
*XDIM_MEMBERSET TIME=%NEXTPER%
//Result: *XDIM_MEMBERSET TIME=2007.05

The same way we calculate previous TIME member:

//%TIME_SET%=2007.04 
*SELECT(%TID%,[TIMEID],TIME,ID=%TIME_SET%) //%TID%=20070400
*SELECT(%TIDL%,[TIMEID],TIME,TIMEID<%TID%) //%TIDG%=20070300,20070200,...
*SELECT(%PREVPER%,[ID],TIME,TIMEID>=%TIDL% AND TIMEID<%TID%)
*XDIM_MEMBERSET TIME=%PREVPER%
//Result: *XDIM_MEMBERSET TIME=2007.03

Same approach can be used to calculate next-next… or prev-prev… member.

Also next (or previous) year value can be calculated:

// Next Year
//%TIME_SET%=2007.04
*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) //%Y%=2007
*SELECT(%TIDM12%,[TIMEID],TIME,YEAR=%Y% AND MONTHNUM=12 AND CALC=N) //%TIDM12%=20071200
*SELECT(%TIDG%,[TIMEID],TIME,TIMEID>%TIDM12%) //%TIDG%=20080100,20080200,...
*SELECT(%NEXTYEAR%,[YEAR],TIME,TIMEID>%TIDM12% AND TIMEID<=%TIDG%)
*XDIM_MEMBERSET TIME=%NEXTYEAR%.01
//Result: *XDIM_MEMBERSET TIME=2008.01 variable %NEXTYEAR%=2008

// Previous Year
//%TIME_SET%=2007.04
*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%) //%Y%=2007
*SELECT(%TIDM01%,[TIMEID],TIME,YEAR=%Y% AND MONTHNUM=1 AND CALC=N) //%TIDM01%=20070100
*SELECT(%TIDL%,[TIMEID],TIME,TIMEID<%TIDM01%) //%TIDL%=...20061100,20061200
*SELECT(%PREVYEAR%,[YEAR],TIME,TIMEID>=%TIDL% AND TIMEID<%TIDM01%)
*XDIM_MEMBERSET TIME=%PREVYEAR%.01
//Result: *XDIM_MEMBERSET TIME=2006.01 variable %PREVYEAR%=2006

Same for next-next… or prev-prev… year.

Questions? Comments?