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

[former_member186338]

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?