Deriving YTD from Periodic data

[Former Member]

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

ytd from per.txt.zip

[former_member186338 · Accepted solution]

Hi Shrikant,

Before discussing the script can you explain the requirements?

You have original PERIODIC data loaded in MIRV1 = V1AV?

You want to have YTD result in MIRV1 = V1CV?

You want to run this script as DM package with user selected YEAR in $TIM1$?

...

Please read

Vadim

P.S. Not sure that RUNALLOCATION is the best option here

[Former Member · Accepted solution]

Hi Vadim,

Always a bit difficult to explain on scn while keeping it brief! But needs must. To answer your questions:

PERIODIC data is loaded to MIRV1=V1AV and I do intend to ask the users to select the year in $TIM1$. But I would like the YTD to also be created in V1AV.

V1CV is just a copy of V1AV for a separate set of users (so can be discounted for this discussion; logic is just so whenever a posting happens to V1AV it also happens to V1CV. I should have taken that bit out; I realise now that it is confusing).

Requirement as it stands:

1. when a record comes from base BW, it will be loaded in respective month in BPC cube (standard UI data load package)

2. when ytd_conversion is run, script should post that number to all future months of the year. It should post with reference to the same dimension members

The two problems I am now facing with above script are:

a. The issue with the delta posting rather than a full record. I might have a way around this problem by using MIRV2 as a counter. Still need to test, but then I now have problem b.

b. Since I am passing the $TIM1$ variable, the script posts for all months regardless of the month I would run it in. E.g. if I run in calendar m/o Jan, I want script to post YTD from Jan-Dec. If I run in calendar m/o June, I want script to post YTD from Jun-Dec. Currently, it always runs for the full year. I need to think about adding a variable so TIME can be select input and then run only for remaining months of the year.

My base query though still remains: Is it possible to post with a REC() function, or ALLOCATION() or some other where the signdata appends, rather than overwrites the previous entries? 'Coz that is what seems to be happening regardless of which feature I have used.

regards

shrikant

PS: I have tried with the REC() option also, but rather than add up the numbers, the system still just seems to be posting a delta.

[former_member186338 · Accepted solution]

"where the signdata appends, rather than overwrites the previous entries" - it's possible with REC like:

%VALUE%+[TIME].[$TIM1$.02],TIME=$TIM1$.02

But I absolutely not recommend this approach!

The correct idea is to keep data from BW as is in some AUDITTRAIL member and the script will generate YTD result in another AUDITTRAIL member.

Vadim

P.S. Slightly unclear what do you mean by "signdata appends"

[former_member186338 · Accepted solution]

For the whole year with YTD data created in V1CV the script will be:

*XDIM_MEMBERSET PDO_CE = <ALL>

*XDIM_MEMBERSET PDO_CAT = BAS(CAT_USR)

*XDIM_MEMBERSET PDO_CUR = USD

*XDIM_MEMBERSET PDO_ENT_01 = <ALL>

*XDIM_MEMBERSET PDO_KF = Q01, V01

*XDIM_MEMBERSET PDO_MIRV1 = V1AV

*XDIM_MEMBERSET PDO_MIRV2 = V2001

*XDIM_MEMBERSET PDO_MIRV3 = VAR3NONE

*XDIM_MEMBERSET PDO_MIRV4 = VAR4NONE

*XDIM_MEMBERSET TIME=$TIM1$.01,$TIM1$.02,$TIM1$.03,$TIM1$.04,$TIM1$.05,$TIM1$.06,$TIM1$.07,$TIM1$.08,$TIM1$.09,$TIM1$.10,$TIM1$.11,$TIM1$.12

*WHEN TIME.MONTHNUM

*IS 1

*FOR %M1%=01,02,03,04,05,06,07,08,09,10,11,12

*REC(EXPRESSION=%VALUE%,TIME=$TIM1$.%M1%,PDO_MIRV1=V1CV)

*NEXT

*IS 2

*FOR %M2%=02,03,04,05,06,07,08,09,10,11,12

*REC(EXPRESSION=%VALUE%,TIME=$TIM1$.%M2%,PDO_MIRV1=V1CV)

*NEXT

*IS 3

*FOR %M2%=03,04,05,06,07,08,09,10,11,12

*REC(EXPRESSION=%VALUE%,TIME=$TIM1$.%M3%,PDO_MIRV1=V1CV)

*NEXT

...

*IS 12

*FOR %M12%=12

*REC(EXPRESSION=%VALUE%,TIME=$TIM1$.%M12%,PDO_MIRV1=V1CV)

*NEXT

*ENDWHEN

Single WHEN/ENDWHEN loop

Vadim

[former_member186338 · Accepted solution]

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

[Former Member · Accepted solution]

Very helpful, this was what I was looking for.

One query though - for these two lines in the script:

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

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

If I write as is, I get the error: "Dimension set: "TIME" not assigned in Data Manager"

So, I made this change (I saw another post where you mention TIME_SET shouldnt be used:

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

*FOR %Y%=%Y1%

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

*FOR %TID%=%TID1%

That doesnt work either. Any suggestions?

[former_member186338 · Accepted solution]

You have to create prompt in the advanced script - to prevent this error:

"Dimension set: "TIME" not assigned in Data Manager"

PROMPT(SELECTINPUT,%SELECTION%,,"Select Month","%TIME_DIM%")

and

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SELECTION, %SELECTION%)

if you use DEFAULT_FORMULAS chain.

Your changes are incorrect!

Please provide the text of your advanced script of DM package!

Vadim

[Former Member · Accepted solution]

Thats what I am using at the moment.

(SELECTINPUT,,,,"%CATEGORY_DIM%,%TIME_DIM%")

(TEXT, %TIM1%,"Year",)

(%EQU%,=)

(%TAB%,;)

(/CPMB/ICDATA_LOGIC,SUSER,%USER%)

(/CPMB/ICDATA_LOGIC,SAPPSET,%APPSET%)

(/CPMB/ICDATA_LOGIC,SAPP,%APP%)

(/CPMB/ICDATA_LOGIC,REPLACEPARAM,TIM1%EQU%%TIM1%)

(/CPMB/ICDATA_LOGIC,LOGICFILENAME,YTDCREATION3.LGF)

[former_member186338 · Accepted solution]

Remove lines:

PROMPT(TEXT, %TIM1%,"Year",)

INFO(%EQU%,=)

INFO(%TAB%,;)

TASK(/CPMB/ICDATA_LOGIC,REPLACEPARAM,TIM1%EQU%%TIM1%)

Add line

TASK(/CPMB/ICDATA_LOGIC,SELECTION, %SELECTION%)

Read help: Defining Dynamic Script Instructions - SAP BusinessObjects Planning and Consolidation - SAP Library

Vadim

[former_member186338]

P.S Please read my answers here:

To convert the full year the code will be simpler...