PERIODIC <-> YTD conversion using script logic in ...

former_member186338 · ‎11-02-2018

Updated on 2019.07.18: Scripts corrected to support not calendar fiscal years!

There is a common requirement to perform PERIODIC <-> YTD conversion of the data:

Copy planning data from PERIODIC model to YTD consolidation model

Copy actual data from YTD consolidation model to PERIODIC planning model

Special currency conversion

Calculation of accounts

...

The period selection has to be the list of months starting from first month of the year up to some selected month or all months of the year.

Let's start with PERIODIC -> YTD conversion:

Sample 1: We have data in EXP account in PERIODIC model and we want to convert this data to YTD and store result in the AST account of the same model for all months of the year.

Somebody may think that the following script will do the job:

*XDIM_MEMBERSET ACCOUNT=PL110 //EXP ACCTYPE

*XDIM_MEMBERSET TIME=BAS(2007.TOTAL)

*XDIM_MEMBERSET MEASURES=PERIODIC



*WHEN TIME

*IS *

*REC(EXPRESSION = [MEASURES].[YTD],ACCOUNT=BS111) //BS111 - AST ACCTYPE

*ENDWHEN

The report screenshot will show the issue:

Empty records for BS111 in the periods with empty records for PL110 (no record in WHEN/ENDWHEN loop) - compared to YTD MEASURE for PL110.

To get the correct results (same as YTD MEASURE of PL110) we need a script that will accumulate figures for all periods like:

*XDIM_MEMBERSET ACCOUNT=PL110 //EXP ACCTYPE

*XDIM_MEMBERSET TIME=BAS(2007.TOTAL)

*XDIM_MEMBERSET MEASURES=PERIODIC

*SELECT(%Y%,[YEAR],TIME,[ID]=2007.TOTAL) //%Y% - will contain 2007

*SELECT(%M01%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=1 AND [CALC]=N)

*SELECT(%M02%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=2 AND [CALC]=N)

*SELECT(%M03%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=3 AND [CALC]=N)

*SELECT(%M04%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=4 AND [CALC]=N)

*SELECT(%M05%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=5 AND [CALC]=N)

*SELECT(%M06%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=6 AND [CALC]=N)

*SELECT(%M07%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=7 AND [CALC]=N)

*SELECT(%M08%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=8 AND [CALC]=N)

*SELECT(%M09%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=9 AND [CALC]=N)

*SELECT(%M10%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=10 AND [CALC]=N)

*SELECT(%M11%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=11 AND [CALC]=N)

*SELECT(%M12%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=12 AND [CALC]=N)



*WHEN TIME.MONTHNUM

*IS 1

*REC(EXPRESSION=%VALUE%,TIME=%M01%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M02%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M03%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M04%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M05%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M06%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M07%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M08%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*IS 2

*REC(EXPRESSION=%VALUE%,TIME=%M02%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M03%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M04%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M05%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M06%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M07%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M08%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*IS 3

*REC(EXPRESSION=%VALUE%,TIME=%M03%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M04%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M05%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M06%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M07%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M08%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*IS 4

*REC(EXPRESSION=%VALUE%,TIME=%M04%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M05%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M06%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M07%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M08%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*IS 5

*REC(EXPRESSION=%VALUE%,TIME=%M05%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M06%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M07%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M08%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*IS 6

*REC(EXPRESSION=%VALUE%,TIME=%M06%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M07%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M08%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*IS 7

*REC(EXPRESSION=%VALUE%,TIME=%M07%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M08%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*IS 8

*REC(EXPRESSION=%VALUE%,TIME=%M08%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*IS 9

*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*IS 10

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*IS 11

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*IS 12

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*ENDWHEN

Correct result:

Code performance: code with single WHEN/ENDWHEN loop is significantly faster then code with multiple loops, where each WHEN/ENDWHEN will calculate single target YTD month value:

//Slow code with 12 WHEN/ENDWHEN loops

*XDIM_MEMBERSET ACCOUNT=PL110

*XDIM_MEMBERSET MEASURES=PERIODIC

*SELECT(%Y%,[YEAR],TIME,[ID]=2007.TOTAL) //%Y% - will contain 2007

*SELECT(%M01%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=1 AND [CALC]=N)

*SELECT(%M02%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=2 AND [CALC]=N)

*SELECT(%M03%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=3 AND [CALC]=N)

*SELECT(%M04%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=4 AND [CALC]=N)

*SELECT(%M05%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=5 AND [CALC]=N)

*SELECT(%M06%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=6 AND [CALC]=N)

*SELECT(%M07%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=7 AND [CALC]=N)

*SELECT(%M08%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=8 AND [CALC]=N)

*SELECT(%M09%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=9 AND [CALC]=N)

*SELECT(%M10%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=10 AND [CALC]=N)

*SELECT(%M11%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=11 AND [CALC]=N)

*SELECT(%M12%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=12 AND [CALC]=N)



*XDIM_MEMBERSET TIME=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%,%M08%,%M09%,%M10%,%M11%,%M12%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M12%,P_ACCOUNT=BS111)

*ENDWHEN



*XDIM_MEMBERSET TIME=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%,%M08%,%M09%,%M10%,%M11%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M11%,P_ACCOUNT=BS111)

*ENDWHEN



*XDIM_MEMBERSET TIME=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%,%M08%,%M09%,%M10%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M10%,P_ACCOUNT=BS111)

*ENDWHEN



*XDIM_MEMBERSET TIME=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%,%M08%,%M09%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M09%,P_ACCOUNT=BS111)

*ENDWHEN



*XDIM_MEMBERSET TIME=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%,%M08%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M08%,P_ACCOUNT=BS111)

*ENDWHEN



*XDIM_MEMBERSET TIME=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M07%,P_ACCOUNT=BS111)

*ENDWHEN



*XDIM_MEMBERSET TIME=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M06%,P_ACCOUNT=BS111)

*ENDWHEN



*XDIM_MEMBERSET TIME=%M01%,%M02%,%M03%,%M04%,%M05%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M05%,P_ACCOUNT=BS111)

*ENDWHEN



*XDIM_MEMBERSET TIME=%M01%,%M02%,%M03%,%M04%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M04%,P_ACCOUNT=BS111)

*ENDWHEN



*XDIM_MEMBERSET TIME=%M01%,%M02%,%M03%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M03%,P_ACCOUNT=BS111)

*ENDWHEN



*XDIM_MEMBERSET TIME=%M01%,%M02%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M02%,P_ACCOUNT=BS111)

*ENDWHEN



*XDIM_MEMBERSET TIME=%M01%

*WHEN TIME

*IS *

*REC(EXPRESSION=%VALUE%,TIME=%M01%,P_ACCOUNT=BS111)

*ENDWHEN

In case of huge amounts of data, each WHEN/ENDWHEN loop in the code can be replaced with *RUNALLOCATION (multiple RUNALLOCATION can be faster then single WHEN/ENDWHEN on big data):

*SELECT(%Y%,[YEAR],TIME,[ID]=2007.TOTAL) //%Y% - will contain 2007

*SELECT(%M01%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=1 AND [CALC]=N)

*SELECT(%M02%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=2 AND [CALC]=N)

*SELECT(%M03%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=3 AND [CALC]=N)

*SELECT(%M04%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=4 AND [CALC]=N)

*SELECT(%M05%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=5 AND [CALC]=N)

*SELECT(%M06%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=6 AND [CALC]=N)

*SELECT(%M07%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=7 AND [CALC]=N)

*SELECT(%M08%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=8 AND [CALC]=N)

*SELECT(%M09%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=9 AND [CALC]=N)

*SELECT(%M10%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=10 AND [CALC]=N)

*SELECT(%M11%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=11 AND [CALC]=N)

*SELECT(%M12%,[ID],TIME,[YEAR]=%Y% AND [MONTHNUM]=12 AND [CALC]=N)



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%,%M08%,%M09%,%M10%,%M11%,%M12%; WHERE=%M12%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%,%M08%,%M09%,%M10%,%M11%; WHERE=%M11%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%,%M08%,%M09%,%M10%; WHERE=%M10%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%,%M08%,%M09%; WHERE=%M09%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%,%M08%; WHERE=%M08%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%,%M07%; WHERE=%M07%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%,%M02%,%M03%,%M04%,%M05%,%M06%; WHERE=%M06%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%,%M02%,%M03%,%M04%,%M05%; WHERE=%M05%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%,%M02%,%M03%,%M04%; WHERE=%M04%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%,%M02%,%M03%; WHERE=%M03%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%,%M02%; WHERE=%M02%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION



*RUNALLOCATION

*FACTOR=1

*DIM TIME WHAT=%M01%; WHERE=%M01%

*DIM P_ACCOUNT WHAT=PL110; WHERE=BS111

*ENDALLOCATION

If we want to convert only number of periods (starting from the first month of the year up to some period selected in user prompt) the script will be:

//%TIME_SET% contain user selection of the last month ex.: 2007.05

*SELECT(%Y%,[YEAR],TIME,[ID]=%TIME_SET%)

*SELECT(%TID%,[TIMEID],TIME,[ID]=%TIME_SET%)

*SELECT(%T01%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND CALC=N)

*SELECT(%TID02%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=2)

*SELECT(%T02%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND TIMEID>=%TID02% AND CALC=N)

*SELECT(%TID03%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=3)

*SELECT(%T03%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND TIMEID>=%TID03% AND CALC=N)

*SELECT(%TID04%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=4)

*SELECT(%T04%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND TIMEID>=%TID04% AND CALC=N)

*SELECT(%TID05%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=5)

*SELECT(%T05%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND TIMEID>=%TID05% AND CALC=N)

*SELECT(%TID06%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=6)

*SELECT(%T06%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND TIMEID>=%TID06% AND CALC=N)

*SELECT(%TID07%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=7)

*SELECT(%T07%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND TIMEID>=%TID07% AND CALC=N)

*SELECT(%TID08%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=8)

*SELECT(%T08%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND TIMEID>=%TID08% AND CALC=N)

*SELECT(%TID09%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=9)

*SELECT(%T09%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND TIMEID>=%TID09% AND CALC=N)

*SELECT(%TID10%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=10)

*SELECT(%T10%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND TIMEID>=%TID10% AND CALC=N)

*SELECT(%TID11%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=11)

*SELECT(%T11%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND TIMEID>=%TID11% AND CALC=N)

*SELECT(%TID12%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=12)

*SELECT(%T12%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND TIMEID>=%TID12% AND CALC=N)



*XDIM_MEMBERSET ACCOUNT=PL110

*XDIM_MEMBERSET MEASURES=PERIODIC



*WHEN TIME.MONTHNUM

*IS 1

*FOR %M01%=%T01%

*REC(EXPRESSION=%VALUE%,TIME=%M01%,ACCOUNT=BS111)

*NEXT

*IS 2

*FOR %M02%=%T02%

*REC(EXPRESSION=%VALUE%,TIME=%M02%,ACCOUNT=BS111)

*NEXT

*IS 3

*FOR %M03%=%T03%

*REC(EXPRESSION=%VALUE%,TIME=%M03%,ACCOUNT=BS111)

*NEXT

*IS 4

*FOR %M04%=%T04%

*REC(EXPRESSION=%VALUE%,TIME=%M04%,ACCOUNT=BS111)

*NEXT

*IS 5

*FOR %M05%=%T05%

*REC(EXPRESSION=%VALUE%,TIME=%M05%,ACCOUNT=BS111)

*NEXT

*IS 6

*FOR %M06%=%T06%

*REC(EXPRESSION=%VALUE%,TIME=%M06%,ACCOUNT=BS111)

*NEXT

*IS 7

*FOR %M07%=%T07%

*REC(EXPRESSION=%VALUE%,TIME=%M07%,ACCOUNT=BS111)

*NEXT

*IS 8

*FOR %M08%=%T08%

*REC(EXPRESSION=%VALUE%,TIME=%M08%,ACCOUNT=BS111)

*NEXT

*IS 9

*FOR %M09%=%T09%

*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)

*NEXT

*IS 10

*FOR %M10%=%T10%

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*NEXT

*IS 11

*FOR %M11%=%T11%

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*NEXT

*IS 12

*FOR %M12%=%T12%

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*NEXT

*ENDWHEN

If we want to convert only number of periods (starting from the period selected in user prompt up to the end of the year) the script will be:

*SELECT(%Y%,YEAR,TIME,ID=%TIME_SET%) //%TIME_SET% - single member!

*SELECT(%TID%,[TIMEID],TIME,ID=%TIME_SET%)

*SELECT(%FYMNTHS%,ID,TIME,YEAR=%Y% AND CALC=N)



*SELECT(%T01%,[ID],TIME,YEAR=%Y% AND TIMEID>=%TID% AND CALC=N)

*SELECT(%TID02%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=2)

*SELECT(%T02%,[ID],TIME,TIMEID>=%TID02% AND ID=%T01%)

*SELECT(%TID03%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=3)

*SELECT(%T03%,[ID],TIME,TIMEID>=%TID03% AND ID=%T01%)

*SELECT(%TID04%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=4)

*SELECT(%T04%,[ID],TIME,TIMEID>=%TID04% AND ID=%T01%)

*SELECT(%TID05%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=5)

*SELECT(%T05%,[ID],TIME,TIMEID>=%TID05% AND ID=%T01%)

*SELECT(%TID06%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=6)

*SELECT(%T06%,[ID],TIME,TIMEID>=%TID06% AND ID=%T01%)

*SELECT(%TID07%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=7)

*SELECT(%T07%,[ID],TIME,TIMEID>=%TID07% AND ID=%T01%)

*SELECT(%TID08%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=8)

*SELECT(%T08%,[ID],TIME,TIMEID>=%TID08% AND ID=%T01%)

*SELECT(%TID09%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=9)

*SELECT(%T09%,[ID],TIME,TIMEID>=%TID09% AND ID=%T01%)

*SELECT(%TID10%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=10)

*SELECT(%T10%,[ID],TIME,TIMEID>=%TID10% AND ID=%T01%)

*SELECT(%TID11%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=11)

*SELECT(%T11%,[ID],TIME,TIMEID>=%TID11% AND ID=%T01%)

*SELECT(%TID12%,[TIMEID],TIME,YEAR=%Y% AND CALC=N AND MONTHNUM=12)

*SELECT(%T12%,[ID],TIME,TIMEID>=%TID12% AND ID=%T01%)



*XDIM_MEMBERSET TIME=%FYMNTHS%



*WHEN TIME.MONTHNUM

*IS 1

*FOR %M01%=%T01%

*REC(EXPRESSION=%VALUE%,TIME=%M01%,ACCOUNT=BS111)

*NEXT

*IS 2

*FOR %M02%=%T02%

*REC(EXPRESSION=%VALUE%,TIME=%M02%,ACCOUNT=BS111)

*NEXT

*IS 3

*FOR %M03%=%T03%

*REC(EXPRESSION=%VALUE%,TIME=%M03%,ACCOUNT=BS111)

*NEXT

*IS 4

*FOR %M04%=%T04%

*REC(EXPRESSION=%VALUE%,TIME=%M04%,ACCOUNT=BS111)

*NEXT

*IS 5

*FOR %M05%=%T05%

*REC(EXPRESSION=%VALUE%,TIME=%M05%,ACCOUNT=BS111)

*NEXT

*IS 6

*FOR %M06%=%T06%

*REC(EXPRESSION=%VALUE%,TIME=%M06%,ACCOUNT=BS111)

*NEXT

*IS 7

*FOR %M07%=%T07%

*REC(EXPRESSION=%VALUE%,TIME=%M07%,ACCOUNT=BS111)

*NEXT

*IS 8

*FOR %M08%=%T08%

*REC(EXPRESSION=%VALUE%,TIME=%M08%,ACCOUNT=BS111)

*NEXT

*IS 9

*FOR %M09%=%T09%

*REC(EXPRESSION=%VALUE%,TIME=%M09%,ACCOUNT=BS111)

*NEXT

*IS 10

*FOR %M10%=%T10%

*REC(EXPRESSION=%VALUE%,TIME=%M10%,ACCOUNT=BS111)

*NEXT

*IS 11

*FOR %M11%=%T11%

*REC(EXPRESSION=%VALUE%,TIME=%M11%,ACCOUNT=BS111)

*NEXT

*IS 12

*FOR %M12%=%T12%

*REC(EXPRESSION=%VALUE%,TIME=%M12%,ACCOUNT=BS111)

*NEXT

*ENDWHEN

Sample 2: We have data in EXP account in PERIODIC model Planning and we want to convert this data to YTD and store result in the same EXP account of the different YTD model PlanYTD.

In this case the following code will work properly:

*XDIM_MEMBERSET ACCOUNT=PL110 //EXP ACCTYPE

*XDIM_MEMBERSET TIME=BAS(2007.TOTAL)

*XDIM_MEMBERSET MEASURES = YTD //Target model storage type



*DESTINATION_APP = PlanYTD //YTD model



*WHEN TIME

*IS *

*REC(EXPRESSION = %VALUE%)

*ENDWHEN

Correct result in YTD model PlanYTD:

It happens because WHEN/ENDWHEN will loop on YTD measure of model Planning. Different approach compared to Sample1.

To convert only number of periods (starting from the first month of the year up to some period selected in user prompt) the script will be:

//%TIME_SET% contain user selection of the last month ex.: 2007.05

*SELECT(%TID%,[TIMEID],TIME,[ID]=%TIME_SET%) 

*SELECT(%T%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND CALC=N)



*XDIM_MEMBERSET TIME=%T%

*XDIM_MEMBERSET ACCOUNT=PL110 //EXP ACCTYPE

*XDIM_MEMBERSET MEASURES = YTD //Target model storage type



*DESTINATION_APP = PlanYTD //YTD model



*WHEN TIME

*IS *

*REC(EXPRESSION = %VALUE%)

*ENDWHEN

Another requirement: YTD -> PERIODIC conversion:

Sample 3: We have data in EXP account (in this case ACCTYPE has no effect - all types are YTD) in YTD model and we want to convert this data to PERIODIC and store result in the EXP account of the same model for all months of the year.

In order to avoid FOR/NEXT loop we have to create a property in TIME dimension like NEXTPER (32 characters) and fill it with member ID's:

The following script will do the job:

*XDIM_MEMBERSET ACCOUNT=PL110

*XDIM_MEMBERSET TIME=BAS(2007.TOTAL)

*XDIM_MEMBERSET MEASURES = YTD



*WHEN TIME.MONTHNUM

*IS 12

*REC(EXPRESSION = %VALUE%,ACCOUNT=PL120)

*ELSE

*REC(EXPRESSION = %VALUE%,ACCOUNT=PL120)

*REC(EXPRESSION = -%VALUE%,TIME=TIME.NEXTPER,ACCOUNT=PL120)

*ENDWHEN

Result:

To convert only number of periods (starting from the first month of the year up to some period selected in user prompt) the script will be:

//%TIME_SET% contain user selection of the last month ex.: 2007.05

*SELECT(%TID%,[TIMEID],TIME,ID=%TIME_SET%)

*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%)

*SELECT(%T%,[ID],TIME,TIMEID<=%TID% AND YEAR=%Y% AND CALC=N)



*XDIM_MEMBERSET TIME=%T%

*XDIM_MEMBERSET ACCOUNT=PL110

*XDIM_MEMBERSET MEASURES = YTD



*WHEN TIME

*IS %TIME_SET%

*REC(EXPRESSION = %VALUE%,ACCOUNT=PL120)

*ELSE

*REC(EXPRESSION = %VALUE%,ACCOUNT=PL120)

*REC(EXPRESSION = -%VALUE%,TIME=TIME.NEXTPER,ACCOUNT=PL120)

*ENDWHEN

Sample 4: We have data in EXP account (in this case ACCTYPE has no effect - all types are YTD) in YTD model PlanYTD and we want to convert this data to PERIODIC and store result in the same EXP account of the different PERIODIC model Planning for all months of the year.

Simple code will work:

*XDIM_MEMBERSET TIME=BAS(2007.TOTAL)

*XDIM_MEMBERSET ACCOUNT=PL110

*XDIM_MEMBERSET MEASURES = PERIODIC



*DESTINATION_APP = Planning



*WHEN TIME

*IS *

*REC(EXPRESSION = %VALUE%)

*ENDWHEN

Result in PERIODIC model Planning:

It happens because WHEN/ENDWHEN will loop on PERIODIC measure of model PlanYTD.

To convert only number of periods (starting from the first month of the year up to some period selected in user prompt) the script will be:

//%TIME_SET% contain user selection of the last month ex.: 2007.05

*SELECT(%TID%,[TIMEID],TIME,[ID]=%TIME_SET%) 

*SELECT(%T%,[ID],TIME,YEAR=%Y% AND TIMEID<=%TID% AND CALC=N)



*XDIM_MEMBERSET TIME=%T%

*XDIM_MEMBERSET ACCOUNT=PL110

*XDIM_MEMBERSET MEASURES = PERIODIC



*DESTINATION_APP = Planning



*WHEN TIME

*IS *

*REC(EXPRESSION = %VALUE%)

*ENDWHEN

Other method to perform PERIODIC <-> YTD conversion is to write Custom Logic badi.

Questions? Comments?

PERIODIC <-> YTD conversion using script logic in BPC NW

SAP Access Control - Useful Documents, Blogs, Resources, etc.

Helpful transactions, tools, programs, tables, etc. for a SAP GRC Consultant

Configure Emergency Access (EAM) in GRC 10