Solved: BPC Script Logic - Roll-over Forecast Values

Hi Experts,

We are implementing BPC 10 NW - release 801 level 3 - we are building a forecasting solution whereby we need a script that can roll-over actuals+forecast values from 1 period to the next.

I have attached a spreadsheet explaining how forecasting / actuals work and we need help or any guidance on how the script logic should look like if indeed it's the best way to archive the goal.

For example:

Start of a project - Version 0 / P1

Budget Actual Forecast - P1 P2 P3 P4 and so on

20000 0 20000 1000 1000 1000 2000

July - Version P2

Budget Actual Forecast - P1 P2 P3 P4 and so on

20000 900 19100 900 (actual) 1050 1050 2000

Aug - Version P3

Budget Actual Forecast - P1 P2 P3 P4 and so on

20000 1900 19100 900 (actual) 1000 (actual) 1100 2000

Any help will be much appreciated on how to archive this scenario.

Thx,

Shane

SAP Managed Tags:
SAP Business Planning and Consolidation, version for SAP NetWeaver

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (1)

But in order to do this they must be in the same dimension right??

If so do you have a sample of the script logic for the above scenario

thx again for your help Vadim

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

What do you mean by "But in order to do this they must be in the same dimension right??"?

Sorry what I was meant to say was.....in order to achieve what you described above actuals / budget / forecast versions will need to be in the same dimension is that correct??

If your answer is yes, do you perhaps have an example of the script logics that can achieve that?

It's not required, but it will simplify things dramatically.

For the example, please provide some sample description - what you want to copy and where....

In my original posting (top of the page) i have given an example of the beginning of the project v0/v1 which has all forecast data and no actuals and then we need a scrip (as per your explination) whereby we Need to copy this forecast actual months from ACTUAL and planned months from previous forecast to version 2 and then in aug (forecast version 3) etc etc

hope that clear

Simple example:

We have:

ACTUAL 2013.01-2013.09

FORECAST08 2013.01-2013.08 from ACTUAL and 2013.09-2013.12 - planned

We want to create:

FORECAST09 2013.01-2013.09 from ACTUAL and 2013.10-2013.12 - from FORECAST08

Then the script will be:

//Clear data to be able to run multiple times

*XDIM_MEMBERSET CATEGORY=FORECAST09

*XDIM_MEMBERSET TIME=BAS(2013.TOTAL)

*WHEN CATEGORY

*IS *

*REC(EXPRESSION=0)

*ENDWHEN

//Copy from ACTUAL 2013.01-2013.09

*XDIM_MEMBERSET CATEGORY=ACTUAL

*XDIM_MEMBERSET TIME=2013.01,2013.02,2013.03,2013.04,2013.05,2013.06,2013.07,2013.08,2013.09

*WHEN CATEGORY

*IS *

*REC(EXPRESSION=%VALUE%,CATEGORY=FORECAST09)

*ENDWHEN

//Copy from FORECAST08 2013.10-2013.12

*XDIM_MEMBERSET CATEGORY=FORECAST08

*XDIM_MEMBERSET TIME=2013.10,2013.11,2013.12

*WHEN CATEGORY

*IS *

*REC(EXPRESSION=%VALUE%,CATEGORY=FORECAST09)

*ENDWHEN

Vadim

Excellent thx Vadim that looks like what we need ill try that and let you know if we have any issues....thx again for your time and help....its much appreciated

Hi Vadim,

I have a question with regards to the below code - lets say for example this specific project has a forecast from 2013.10 thru to 2015.12 will all those periods need to be hard coded or is it possible to make the time dynamic for example: > (greater than) a specific period, in this case 2013.10

//Copy from FORECAST08 2013.10-2013.12

*XDIM_MEMBERSET CATEGORY=FORECAST08

*XDIM_MEMBERSET TIME=2013.10,2013.11,2013.12

*WHEN CATEGORY

*IS *

*REC(EXPRESSION=%VALUE%,CATEGORY=FORECAST09)

*ENDWHEN

And as we have 12 Forecast Versions - will we need to create 12 different script logic for each "roll-over" Version ??

Thx

Hi Shane,

You can dynamically choose all periods greater then a selected time period using a couple of select statements.

The first you'll need to return the TIMEID of the selected time period.

The second you'll need to return all members who's TIMEID is greater then the value returned in the first select statement.

You can then use all the values returned in the second select statement in your equation.

Let me know if you want any help on this.

Cheers,

Chris

Hi Chris,

Thanks for your reply - and I'm new to script logic so any help is much appreciated pls, are you able to provide me with some sample code pls

and assuming we could set the forecast versions dynamically too so for example - select a time, version etc - is that correct?

Thx,

Shane

Hi Shane,

The idea is to have some property of the category dimension to specify the first planned month. Example STARTMONTH. Also you can have property to specify previous forecast - PREVFCST Then using select you can:

//%CATEGORY_SET% - contains FORECAST09, STARTMONTH = 10, PREVFC=FORECAST08

*SELECT(%SM%,"[STARTMONTH]",CATEGORY,"[ID]='%CATEGORY_SET%')

//$YY$ - contain planning year selected by user from DM combobox

*SELECT(%FCSTMNTH%,"[ID]",TIME,"[ID]>'$YY$.%SM%'")

*SELECT(%ACTMNTH%,"[ID]",TIME,"[ID]<'$YY$.%SM%'")

*SELECT(%PF%,"[PREVFCST]",CATEGORY,"[ID]='%CATEGORY_SET%')

//To copy FORECASTXX we use

*XDIM_MEMBERSET CATEGORY=%PF%

*XDIM_MEMBERSET TIME=$YY$.%SM%,%FCSTMNTH%

//To copy ACTUAL we use

*XDIM_MEMBERSET CATEGORY=ACTUAL

*XDIM_MEMBERSET TIME=%ACTMNTH%

Vadim

Hi Vadim,

Not sure I follow with regards to the property for STARTMONTH and PREVFCST in the Dimension are you able to give me an example pls

I know you mentioned added complexity having 1 Dim for CATEGORY and 1 Dim for VERSION but we are unable to combine the 2 at the stage, I have tried altering your code to be:

//Clear data to be able to run multiple times

*XDIM_MEMBERSET F_VERSION=P9

*XDIM_MEMBERSET TIME=BAS(2013.TOTAL)

*WHEN F_VERSION

*IS *

*REC(EXPRESSION=0)

*ENDWHEN

//Copy from ACTUAL 2013.01-2013.09

*XDIM_MEMBERSET F_CATEGORY=ACTUAL

*XDIM_MEMBERSET TIME=2013.001,2013.002,2013.003,2013.004,2013.005,2013.006,2013.007,2013.008,2013.009

*WHEN F_CATEGORY

*IS *

*REC(EXPRESSION=%VALUE%,F_VERSION=P9)

*ENDWHEN

//Copy from FORECAST P8 2013.10-2013.12

*XDIM_MEMBERSET F_VERSION=P8

*XDIM_MEMBERSET TIME=2013.010,2013.011,2013.012

*WHEN F_VERSION

*IS *

*REC(EXPRESSION=%VALUE%,F_VERSION=P9)

*ENDWHEN

But it's bringing back zero records and always referencing back to P9 which is hard coded.

and how would you fill the property STARTMONTH and PREVFCST in the Dim with those values?

Sorry, I do not want to discuss setup with additional dimension for version. It's simply not the right way.

Hi Vadim - you're right, I'm changing it now, I'll get back to you once I'm done - thx again

Hi Vadim - ok we have now got DIM F_CATEGORY with Forecast Versions as members of that DIM - are you pls able to continue helping us?

It looks like none of my answers are helpful (at least not marked)

Hi Vadim,

As mentioned we now have 1 DIM with Forecast versions as member and it actually makes more sense - thx for pushing the subject.

I've validated the below logic and have a quick question:

So with regards to STARTMNTH and PREVFCST as DIM properties - would these need to be maintained or should they be a prompt when running the script?

//%CATEGORY_SET% - contains FORECAST09, STARTMONTH = 10, PREVFC=FORECAST08

*SELECT(%SM%,"[STARTMONTH]",CATEGORY,"[ID]='%CATEGORY_SET%')

//$YY$ - contain planning year selected by user from DM combobox

*SELECT(%FCSTMNTH%,"[ID]",TIME,"[ID]>'$YY$.%SM%'")

*SELECT(%ACTMNTH%,"[ID]",TIME,"[ID]<'$YY$.%SM%'")

*SELECT(%PF%,"[PREVFCST]",CATEGORY,"[ID]='%CATEGORY_SET%')

//To copy FORECASTXX we use

*XDIM_MEMBERSET CATEGORY=%PF%

*XDIM_MEMBERSET TIME=$YY$.%SM%,%FCSTMNTH%

//To copy ACTUAL we use

*XDIM_MEMBERSET CATEGORY=ACTUAL

*XDIM_MEMBERSET TIME=%ACTMNTH%

For the standard copy DM it's better to maintain the proposed properties. User will be only prompted for target CATEGORY and planning year.

Vadim

Following this interesting Thread.

A quick question on the script line: *SELECT(%ACTMNTH%,"[ID]",TIME,"[ID]<'$YY$.%SM%'").

i am probably missing a scope on time somewhere: this statement could pick up previous year periods too, right?

Thanks.

You have lost me so we should be using DM copy instead of script logic

By "standard copy" I was talking about script logic - standard in terms of every month execution or so...

Vadim

Ups, yes I forget to include some year dependent conditions, the full selection line will be like:

*SELECT(%ACTMNTH%,"[ID]",TIME,"[ID]<'$YY$.%SM%' AND [YEAR]='$YY$' AND [CALC]='N'")

the same for other selection:

*SELECT(%FCSTMNTH%,"[ID]",TIME,"[ID]>'$YY$.%SM%' AND [YEAR]='$YY$' AND [CALC]='N'")

Hi Vadim

I've maintained ACTMNTH & PREVFCST in the CATEGORY Dim as follows:

PREVFCST STARTMNTH

P1 Forecast Version 1

P2 Forecast Version 2 P1 2

P3 Forecast Version 3 P2 3

P4 Forecast Version 4 P3 4

P5 Forecast Version 5 P4 5

P6 Forecast Version 6 P5 6

P7 Forecast Version 7 P6 7

P8 Forecast Version 8 P7 8

P9 Forecast Version 9 P8 9

P10 Forecast Version 10 P9 10

P11 Forecast Version 11 P10 11

P12 Forecast Version 12 P11 12

When I run the below script it returns zero results - am I missing something, and the only prompt I have is CATEGORY, can you pls advise?

//%CATEGORY_SET% - contains Forecast P9, STARTMNTH = 10, PREVFC=P8

*SELECT(%SM%,"[STARTMNTH]",F_CATEGORY,"[ID]='%F_CATEGORY_SET%')

//$YY$ - contain planning year selected by user from DM combobox

*SELECT(%FCSTMNTH%,"[ID]",TIME,"[ID]>'$YY$.%SM%' AND [YEAR]='$YY$' AND [CALC]='N'")

*SELECT(%ACTMNTH%,"[ID]",TIME,"[ID]<'$YY$.%SM%' AND [YEAR]='$YY$' AND [CALC]='N'")

*SELECT(%PF%,"[PREVFCST]",F_CATEGORY,"[ID]='%F_CATEGORY_SET%')

//To copy FORECAST XX we use

*XDIM_MEMBERSET F_CATEGORY=%PF%

*XDIM_MEMBERSET TIME=$YY$.%SM%,%FCSTMNTH%

//To copy ACTUAL we use

*XDIM_MEMBERSET F_CATEGORY=ACTUAL

*XDIM_MEMBERSET TIME=%ACTMNTH

Sorry, but what is P1-P12 - how your time dimension looks like?

Show the screenshot of the category and time dims.

Vadim

Then

STARTMNTH need to contain:

001

002

...

012

Vadim

And please, provide test result in UJKT:

Hi Vadim,

Pls find attached the test log - and once again I appreciate your help

I'm not sure if I have the comobox correct

Ups, looks like you don't read the provided document about UJKT usage:

You need to properly fill:

PARAM field: YY=2014 this will be used as a value of $YY$ variable.

Data Region: F_CATEGORY=P9 this will be used as a value of %F_CATEGORY_SET% variable

Also for UJKT don show screenshot, just copy contents of:

PARAM

Data Region

Script text

Log

Please post results

And don't forget to correct values of STARTMNTH property - have to be a month part of TIME ID.

Vadim

P.S. Don't spend time on DM combobox logic, first ensure that everything is running in UJKT

Hi Vadim

I did correct the STARTMNTH property to 001 002, etc (thx)

PARAM - YY=2014

DATA REGION - F_CATEGORY=P9

SCRIPT TEXT

//%CATEGORY_SET% - contains P9, STARTMNTH = 10, PREVFC=P8

*SELECT(%SM%,"[STARTMNTH]",F_CATEGORY,"[ID]='%F_CATEGORY_SET%')

//$YY$ - contain planning year selected by user from DM combobox

*SELECT(%FCSTMNTH%,"[ID]",TIME,"[ID]>'$YY$.%SM%' AND [YEAR]='$YY$' AND [CALC]='N'")

*SELECT(%ACTMNTH%,"[ID]",TIME,"[ID]<'$YY$.%SM%' AND [YEAR]='$YY$' AND [CALC]='N'")

*SELECT(%PF%,"[PREVFCST]",F_CATEGORY,"[ID]='%F_CATEGORY_SET%')

//To copy FORECAST XX we use

*XDIM_MEMBERSET F_CATEGORY=%PF%

*XDIM_MEMBERSET TIME=$YY$.%SM%,%FCSTMNTH%

//To copy ACTUAL we use

*XDIM_MEMBERSET F_CATEGORY=ACTUAL

*XDIM_MEMBERSET TIME=%ACTMNTH%

LOG

LGX:

*XDIM_MEMBERSET F_CATEGORY=P8

*XDIM_MEMBERSET TIME=2014.009,2014.010,2014.011,2014.012

*XDIM_MEMBERSET F_CATEGORY=ACTUAL

*XDIM_MEMBERSET TIME=2014.001,2014.002,2014.003,2014.004,2014.005,2014.006,2014.007,2014.008

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2014-03-07 16:51:17

FILE:\ROOT\WEBFOLDERS\F_COUNCIL \ADMINAPP\Forecasting\TEST.LGF

USER:096655

APPSET:F_COUNCIL

APPLICATION:Forecasting

SCRIPT RUNNING TIME IN TOTAL:0.32 s.

LOG END TIME:2014-03-07 16:51:17

As you can see the result in

LGX:

*XDIM_MEMBERSET F_CATEGORY=P8

*XDIM_MEMBERSET TIME=2014.009,2014.010,2014.011,2014.012

*XDIM_MEMBERSET F_CATEGORY=ACTUAL

*XDIM_MEMBERSET TIME=2014.001,2014.002,2014.003,2014.004,2014.005,2014.006,2014.007,2014.008

is fine, the scope is correct.

Then you need to add code (in bold) that will actually write values:

//%CATEGORY_SET% - contains P9, STARTMNTH = 10, PREVFC=P8

*SELECT(%SM%,"[STARTMNTH]",F_CATEGORY,"[ID]='%F_CATEGORY_SET%')

//$YY$ - contain planning year selected by user from DM combobox

*SELECT(%FCSTMNTH%,"[ID]",TIME,"[ID]>'$YY$.%SM%' AND [YEAR]='$YY$' AND [CALC]='N'")

*SELECT(%ACTMNTH%,"[ID]",TIME,"[ID]<'$YY$.%SM%' AND [YEAR]='$YY$' AND [CALC]='N'")

*SELECT(%PF%,"[PREVFCST]",F_CATEGORY,"[ID]='%F_CATEGORY_SET%')

//To copy FORECAST XX we use

*XDIM_MEMBERSET F_CATEGORY=%PF%

*XDIM_MEMBERSET TIME=$YY$.%SM%,%FCSTMNTH%

*WHEN F_CATEGORY

*IS *

*REC(EXPRESSION=%VALUE%,F_CATEGORY=%F_CATEGORY_SET%)

*ENDWHEN

//To copy ACTUAL we use

*XDIM_MEMBERSET F_CATEGORY=ACTUAL

*XDIM_MEMBERSET TIME=%ACTMNTH%

*WHEN F_CATEGORY

*IS *

*REC(EXPRESSION=%VALUE%,F_CATEGORY=%F_CATEGORY_SET%)

*ENDWHEN

Test it in UJCT!

B.R. Vadim

Hi Vadim,

pls see the LOG below:

LGX:

*XDIM_MEMBERSET F_CATEGORY=P8

*XDIM_MEMBERSET TIME=2014.009,2014.010,2014.011,2014.012

*WHEN F_CATEGORY

*IS *

*REC(EXPRESSION=%VALUE%,F_CATEGORY=P9)

*ENDWHEN

*XDIM_MEMBERSET F_CATEGORY=ACTUAL

*XDIM_MEMBERSET TIME=2014.001,2014.002,2014.003,2014.004,2014.005,2014.006,2014.007,2014.008

*WHEN F_CATEGORY

*IS *

*REC(EXPRESSION=%VALUE%,F_CATEGORY=P9)

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2014-03-07 17:13:18

FILE:\ROOT\WEBFOLDERS\F_COUNCIL \ADMINAPP\Forecasting\TEST.LGF

USER:096655

APPSET:F_COUNCIL

APPLICATION:Forecasting

[INFO] GET_DIM_LIST(): I_APPL_ID="Forecasting", #dimensions=8

F_CATEGORY,F_ENTITY,F_PERSON,F_PROJECT,F_SECTION,F_WBSE,MEASURES,TIME

#dim_memberset=2

F_CATEGORY:P8,1 in total.

TIME:2014.009,2014.010,2014.011,2014.012,4 in total.

REC :%VALUE%

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 1002.01 ms. 72 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :232.96 ms.

72 RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 72 RECORDS ARE GENERATED.

DATA TO WRITE BACK:

F_CATEGORY F_ENTITY F_PERSON F_PROJECT F_SECTION F_WBSE TIME SIGNEDDATA

P9 4000 000000 A-000036 000 A-000036.1 2014.009 34.00

P9 4000 000000 A-000036 000 A-000036.1 2014.010 35.00

P9 4000 000000 A-000036 000 A-000036.1 2014.011 234.00

P9 4000 000000 A-000036 000 A-000036.1 2014.012 234.00

P9 4000 000000 A-000036 000 A-000036.1.01 2014.009 43.00

P9 4000 000000 A-000036 000 A-000036.1.01 2014.010 44.00

P9 4000 000000 A-000036 000 A-000036.1.01 2014.011 3333.00

P9 4000 000000 A-000036 000 A-000036.1.01 2014.012 3333.00

P9 4000 000000 A-000036 000 A-000036.1.01.01 2014.009 59.00

P9 4000 000000 A-000036 000 A-000036.1.01.01 2014.010 60.00

P9 4000 000000 A-000036 000 A-000036.1.01.01 2014.011 2222.00

P9 4000 000000 A-000036 000 A-000036.1.01.01 2014.012 2222.00

P9 4000 000000 A-000036 000 A-000036.1.01.01.01 2014.009 94.00

P9 4000 000000 A-000036 000 A-000036.1.01.01.01 2014.010 85.00

P9 4000 000000 A-000036 000 A-000036.1.01.01.01 2014.011 6733.00

P9 4000 000000 A-000036 000 A-000036.1.01.01.01 2014.012 6733.00

P9 4000 000000 A-000036 000 A-000036.1.01.01.02 2014.009 28.00

P9 4000 000000 A-000036 000 A-000036.1.01.01.02 2014.010 29.00

P9 4000 000000 A-000036 000 A-000036.1.01.01.02 2014.011 6767.00

P9 4000 000000 A-000036 000 A-000036.1.01.01.02 2014.012 6767.00

P9 4000 000000 A-000036 000 A-000036.1.01.02 2014.009 19.00

P9 4000 000000 A-000036 000 A-000036.1.01.02 2014.010 9876.00

P9 4000 000000 A-000036 000 A-000036.1.01.02 2014.011 9876.00

P9 4000 000000 A-000036 000 A-000036.1.01.02 2014.012 9876.00

P9 4000 000000 A-000036 000 A-000036.1.01.02.01 2014.009 222.00

P9 4000 000000 A-000036 000 A-000036.1.01.02.01 2014.010 222.00

P9 4000 000000 A-000036 000 A-000036.1.01.02.01 2014.011 222.00

P9 4000 000000 A-000036 000 A-000036.1.01.02.01 2014.012 222.00

P9 4000 000000 A-000036 000 A-000036.1.01.02.02 2014.009 789.00

P9 4000 000000 A-000036 000 A-000036.1.01.02.02 2014.010 789.00

P9 4000 000000 A-000036 000 A-000036.1.01.02.02 2014.011 789.00

P9 4000 000000 A-000036 000 A-000036.1.01.02.02 2014.012 789.00

P9 4000 000000 A-000036 000 A-000036.1.01.03 2014.009 654.00

P9 4000 000000 A-000036 000 A-000036.1.01.03 2014.010 654.00

P9 4000 000000 A-000036 000 A-000036.1.01.03 2014.011 654.00

P9 4000 000000 A-000036 000 A-000036.1.01.03 2014.012 654.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.01 2014.009 3.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.01 2014.010 3.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.01 2014.011 3.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.01 2014.012 3.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.02 2014.009 22.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.02 2014.010 22.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.02 2014.011 22.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.02 2014.012 22.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.03 2014.009 67.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.03 2014.010 67.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.03 2014.011 67.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.03 2014.012 67.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.04 2014.009 897.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.04 2014.010 897.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.04 2014.011 897.00

P9 4000 000000 A-000036 000 A-000036.1.01.03.04 2014.012 897.00

P9 4000 000000 A-000036 000 A-000036.1.01.04 2014.009 111.00

P9 4000 000000 A-000036 000 A-000036.1.01.04 2014.010 111.00

P9 4000 000000 A-000036 000 A-000036.1.01.04 2014.011 111.00

P9 4000 000000 A-000036 000 A-000036.1.01.04 2014.012 111.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.01 2014.009 383.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.01 2014.010 383.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.01 2014.011 383.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.01 2014.012 383.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.02 2014.009 643.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.02 2014.010 643.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.02 2014.011 643.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.02 2014.012 643.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.03 2014.009 434.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.03 2014.010 434.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.03 2014.011 434.00

P9 4000 000000 A-000036 000 A-000036.1.01.04.03 2014.012 434.00

P9 4000 000000 A-000036 000 A-000036.1.01.05 2014.009 88.00

P9 4000 000000 A-000036 000 A-000036.1.01.05 2014.010 88.00

P9 4000 000000 A-000036 000 A-000036.1.01.05 2014.011 88.00

P9 4000 000000 A-000036 000 A-000036.1.01.05 2014.012 88.00

72 RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :1217.21 ms.

[INFO] GET_DIM_LIST(): I_APPL_ID="Forecasting", #dimensions=8

F_CATEGORY,F_ENTITY,F_PERSON,F_PROJECT,F_SECTION,F_WBSE,MEASURES,TIME

#dim_memberset=2

F_CATEGORY:ACTUAL,1 in total.

TIME:2014.001,2014.002,2014.003,2014.004,2014.005,...8 in total.

REC :%VALUE%

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 736.03 ms. 43 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :202.72 ms.

43 RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 43 RECORDS ARE GENERATED.

DATA TO WRITE BACK:

F_CATEGORY F_ENTITY F_PERSON F_PROJECT F_SECTION F_WBSE TIME SIGNEDDATA

P9 4000 000000 A-000001 000 A-000001.1.01 2014.008 409.09

P9 4000 000000 A-000001 000 A-000001.1.03 2014.005 605.00

P9 4000 000000 A-000001 000 A-000001.1.03 2014.007 275.00

P9 4000 000000 A-000001 000 A-000001.1.03 2014.008 6779.00

P9 4000 000000 A-000005 000 A-000005.1.01.06 2014.005 27520.00

P9 4000 000000 A-000005 000 A-000005.1.02.01 2014.008 5984.00

P9 4000 000000 A-000005 000 A-000005.1.04.01 2014.007 5500.00

P9 4000 000000 A-000005 000 A-000005.1.04.01 2014.008 500.00

P9 4000 000000 A-000005 000 A-000005.2.01 2014.008 9400.00

P9 4000 000000 A-000009 000 A-000009.1.01.01.01 2014.005 6300.00

P9 4000 000000 A-000011 000 A-000011.1.01.01.02 2014.005 37050.00

P9 4000 000000 A-000011 000 A-000011.1.01.01.02 2014.006 3600.00

P9 4000 000000 A-000011 000 A-000011.1.01.02 2014.005 7560.00

P9 4000 000000 A-000011 000 A-000011.1.01.02.01 2014.005 3990.00

P9 4000 000000 A-000011 000 A-000011.1.01.02.01 2014.006 - 3600.00

P9 4000 000000 A-000011 000 A-000011.1.01.02.07 2014.005 12425.00

P9 4000 000000 A-000011 000 A-000011.1.01.02.08 2014.005 5775.00

P9 4000 000000 A-000011 000 A-000011.1.02 2014.005 3400.00

P9 4000 000000 A-000011 000 A-000011.1.02 2014.008 800.00

P9 4000 000001 A-000015 000 A-000015.1.01.02 2014.005 3800.00

P9 4000 000001 A-000015 000 A-000015.1.01.02 2014.006 6840.00

P9 4000 000001 A-000019 000 A-000019.1.01.01.02 2014.007 1600.00

P9 4000 000000 A-000020 000 A-000020.1.01.01 2014.007 - 770.00

P9 4000 000000 A-000020 000 A-000020.1.01.01.01 2014.007 770.00

P9 4000 000001 A-000020 000 A-000020.1.01.01.02 2014.007 150.00

P9 4000 000000 A-000020 000 A-000020.2.01 2014.007 770.00

P9 4000 000001 A-000021 000 A-000021.1.01.01.01 2014.007 2850.00

P9 4000 000001 A-000022 000 A-000022.1.01.01.01 2014.007 75.00

P9 4000 000000 A-000026 001 A-000026.1.01.01.01 2014.007 4900.00

P9 4000 070359 A-000032 000 A-000032.1 2014.008 - 9400.00

P9 4000 000000 A-000032 000 A-000032.1.01.02 2014.008 9400.00

P9 4000 000000 A-000032 000 A-000032.1.01.05.01 2014.007 15500.00

P9 4000 000000 A-000032 000 A-000032.1.01.06.01 2014.008 45070.00

P9 4000 000000 A-000032 000 A-000032.1.02.01 2014.008 30406.00

P9 4000 000000 A-000032 000 A-000032.1.02.02 2014.008 4503.60

P9 4000 000000 A-000036 000 A-000036.1.01.01 2014.007 348840.00

P9 4000 000000 A-000036 000 A-000036.1.01.01 2014.008 188100.00

P9 4000 000000 A-000036 000 A-000036.1.01.05.01 2014.008 32250.00

P9 4000 000000 A-000036 000 A-000036.1.01.06.01 2014.008 3610.00

P9 4000 000000 A-000502 002 A-000502.1.01.01.01.01.1 2014.007 6000.00

P9 4000 000000 A-000502 002 A-000502.1.01.01.01.01.1 2014.008 2080.00

P9 4000 000000 A-000502 000 A-000502.1.01.02.01 2014.008 6875.00

P9 4000 000000 B-000001 000 B-000001.1.01.02 2014.008 100.00

43 RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :1034.24 ms.

SCRIPT RUNNING TIME IN TOTAL:5.04 s.

LOG END TIME:2014-03-07 17:13:23

There's definitely some values now - I will need to reconcile these values......almost there (thank you so much - it's much appreciated)

I'm hoping to click "correct answer" very shortly

To make it work in DM package you have to edit DM advanced script (assuming you create DM for the chain /CPMB/DEFAULT_FORMULAS:

PROMPT(MESSAGE,"Create Forecast Based on Previous")

PROMPT(COMBOBOX,%YY%,"Select Forecast Year:",0,,{2014,2015,2016,2017,2018})

PROMPT(SELECTINPUT,%SELECTION%,,"Select the Forecast Version:","F_CATEGORY")

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SUSER,%USER%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,SAPP,%APP%)

INFO(%EQU%,=)

INFO(%TAB%,;)

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

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

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

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,LOGICFILENAME,FORECAST.LGF)

TASK(/CPMB/DEFAULT_FORMULAS_LOGIC,REPLACEPARAM,YY%EQU%%YY%)

Where FORECAST.LGF is the name of script file.

Vadim

Thx Vadim - I will continue with this on monday thanks once again I appreciate tour time and help.....I will let you know the outcome

thx

shane

Hi Vadim - I just wanted to say a HUGE thank you, it's working like a charm and as expected, thank you again, your time / help is very much appreciated.

Thx,

Shane

Hi Vadim,

Another quick question pls (and everything is working fine - thx) once we have rolled-over the forecast is it possible to highlight the "ACTUAL" values that replace the Forecast values for the previous month so that the user can clearly see that it's an "ACTUAL" value and not a Forecast value?

Thx,

Shane

Hi Shane,

You can get STARTMNTH property in the cell somewhere in the report. Using this value you can try to create conditional formatting Excel formula...

Vadim

Thx Vadim,

I'm being doing some testing regards to the forecast roll-over script which as I mentioned works fine provided you are the same forecast year (Fiscal Year), the issue I have for example:

If I have project TEST and it started in 2013.011 and ends in 2014.004, I have forecast values for all periods so when I run the script I'm prompted to "select forecast year" and "select forecast version based on previous period"

So when I run from 2013.11 (forecast version P11) to 2013.12 (forecast version P12) that works fine but it's not working to roll-over from 2013.012 to 2014.001 - are you able to pls advise?

Thx

Shane

Hi Shane,

Project planning with project start and end ("If I have project TEST and it started in 2013.011 and ends in 2014.004,") is absolutely different story.

You have to describe requirements and may be not in this topic (open a new one).

In general - what is the year scope of your forecasting - 2 years or more? The scripts proposed are for single year scope.

Vadim

Aaagh apologies for that and projects can run for 5 months or up to 4 years or longer

Do you want me to open a new forum?

Yes, it will be better (this topic is too long)!

And it's better to describe significant things from the very beginning...

no problems and once again, apologies...

Answers (3)

Thx Vadim - we have had to seperate them due to various calculations etc but are you able to help in regards to the forecast rollover (copy) ?

Cheers

shane

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Just to avoid having separate dimension for forecast versions... extra dimensions increase complexity and affect performance.

Vadim

Ok so if we have 1 dimension with forecast versions as members would it then be possible to copy or rollover version 1 to version 2?

For sure!

We use script logic packages to copy data between versions.

Example:

We start working with some forecast.

We copy to this forecast actual months from ACTUAL and planned months from previous forecast. Then we start adjustments of planned months saving copies in drafts...

Vadim

Hi Vadim,

I may have intercommunicated my issue - so for example if I have project A starting in Jul 2014 and Forecast Version 1, I'm forecasting my whole project that's going to take 15 months.

So as I want to keep version 1 in tact -> is it possible (without having to re forecast the whole project again) to roll-over Forecast version 1 into version 2, 3 , 4 etc

hope that clear?

Thx,

Shane

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

In our system we have CATEGORY dimension like this:

ACTUAL

BUDGET

BUDGETD01 - Draft 01

BUDGETD02 - Draft 02

BUDGETD03 - Draft 03

...

FORECAST

FORECASTD01 - Draft 01

FORECASTD02 - Draft 02

...

Vadim

Sorry forgot to mention that we Forecast versions P1 -> P12 and Category Actual / Budget / Forecast etc

Thx

Shane

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Shane,

After looking on the dimension structure (from e-mail) I can see a very strange approach:

You have a CATEGORY dimension with Actual, Budget, Forecast...

And you also have VERSION dimension (versions of forecast?)

It looks overcomplicated and creates misunderstanding...

Why not to have single CATEGORY dimension with members for Forecast versions?

Vadim

Ask a Question

Top Q&A Solution Author

User

Count

BPC Script Logic - Roll-over Forecast Values

Accepted Solutions (1)

Accepted Solutions (1)

Answers (3)

Answers (3)

Issue while settling WBS in GJ8G

Re: TRS_SEC_ACC default field for ftr_create (fiel...

Re: Using BAPI_BUPA_FS_BPADDITION_CHANGE to update...

ACH/ WIRE RETURN Process in S/4 Hana

Re: Interest Posting is generated on the same date...