Skip to Content
avatar image
Former Member

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 18, 2015 at 11:59 AM

    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 How To: Ask questions about Script Logic issues

    Vadim

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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 18, 2015 at 12:06 PM

    P.S Please read my answers here:

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

    Add comment
    10|10000 characters needed characters exceeded