Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Deriving YTD from Periodic data

Hi

I have a periodic planning cube that has actuals and budgets loaded to it each month. One requirement is to have a cumulative YTD view of this data. To achieve this, I intend to run some script logic after each month's pull from the base BW cube. This script logic is attached as a text file. I give below an extract of this logic that would run for month 02.

*XDIM_MEMBERSET CE = <ALL>
*XDIM_MEMBERSET CAT = BAS(CAT_USR)
*XDIM_MEMBERSET CUR = USD
*XDIM_MEMBERSET ENT_01 = <ALL>
*XDIM_MEMBERSET KF = Q01, V01
*XDIM_MEMBERSET MIRV1 = V1AV
*XDIM_MEMBERSET MIRV2 = V2001
*XDIM_MEMBERSET MIRV3 = VAR3NONE
*XDIM_MEMBERSET MIRV4 = VAR4NONE

*RUNALLOCATION
*FACTOR = 1
*DIM TIME WHAT=$TIM1$.01; WHERE=<<<; USING=<<<
*DIM MIRV1 WHAT=V1AV; WHERE=V1AV,V1CV;
*ENDALLOCATION

*RUNALLOCATION
*FACTOR = 1
*DIM TIME WHAT=$TIM1$.01,$TIM1$.02; WHERE=$TIM1$.02;
*DIM MIRV1 WHAT=V1AV; WHERE=V1AV,V1CV;
*ENDALLOCATION

With this data, if I were to write $100 to Month 1, it also posts $100 to Month 2. However, when I get around to loading actuals and budgets for Month 2, it only loads a delta record.

Step 1: Load budgets in Month1

Records Posted

2014.01; V1AV; $100

Step 2: Run allocation to change to YTD

Records Posted

2014.02; V1AV; $100

Step 3: Load budgets in Month 2 (suppose I load $150)

Records Posted

2014.02; V1AV; $100 [from previous script logic run]

2014.02; V1AV; $50 [delta loaded. I would like this to remain $150 so I have a new YTD of $250 (100+150)]

Can anyone suggest how this could be attained?

regards

Shrikant

BPC 10.0 NW

Excel add in SP21

Tags:
Former Member
replied

And if you want to select a single month in %TIME_SET% and recalculate only months starting from %TIME_SET% and till the end of the year, the the following script:

*SELECT(%Y%,[YEAR],TIME,[ID]=%TIME_SET%) //Get Year in %Y%

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

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

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

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

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

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

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

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

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

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

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

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

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

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

*XDIM_MEMBERSET TIME=%M% //all months of the year

*WHEN TIME.MONTHNUM

*IS 1

*FOR %N1%=%M1%

*REC(EXPRESSION=%VALUE%,TIME=%N1%,PDO_MIRV1=V1CV)

*NEXT

*IS 2

*FOR %N2%=%M2%

*REC(EXPRESSION=%VALUE%,TIME=%N2%,PDO_MIRV1=V1CV)

*NEXT

*IS 3

*FOR %N3%=%M3%

*REC(EXPRESSION=%VALUE%,TIME=%N3%,PDO_MIRV1=V1CV)

*NEXT

*IS 4

*FOR %N4%=%M4%

*REC(EXPRESSION=%VALUE%,TIME=%N4%,PDO_MIRV1=V1CV)

*NEXT

*IS 5

*FOR %N5%=%M5%

*REC(EXPRESSION=%VALUE%,TIME=%N5%,PDO_MIRV1=V1CV)

*NEXT

*IS 6

*FOR %N6%=%M6%

*REC(EXPRESSION=%VALUE%,TIME=%N6%,PDO_MIRV1=V1CV)

*NEXT

*IS 7

*FOR %N7%=%M7%

*REC(EXPRESSION=%VALUE%,TIME=%N7%,PDO_MIRV1=V1CV)

*NEXT

*IS 8

*FOR %N8%=%M8%

*REC(EXPRESSION=%VALUE%,TIME=%N8%,PDO_MIRV1=V1CV)

*NEXT

*IS 9

*FOR %N9%=%M9%

*REC(EXPRESSION=%VALUE%,TIME=%N9%,PDO_MIRV1=V1CV)

*NEXT

*IS 10

*FOR %N10%=%M10%

*REC(EXPRESSION=%VALUE%,TIME=%N10%,PDO_MIRV1=V1CV)

*NEXT

*IS 11

*FOR %N11%=%M11%

*REC(EXPRESSION=%VALUE%,TIME=%N11%,PDO_MIRV1=V1CV)

*NEXT

*IS 12

*FOR %N12%=%M12%

*REC(EXPRESSION=%VALUE%,TIME=%N12%,PDO_MIRV1=V1CV)

*NEXT

*ENDWHEN

Vadim

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question