Apply variable to script logic FOR / NEXT statement

[Former Member]

Hi experts,

Example:

*FOR %PeriodLoop2%=0,1,2,3,4,5

*REC(EXPRESSION=GET(time=Next(%PeriodLoop2%)),category=%PlanCategory%,PlanItem= #RP420004accl,time=Next(%input%))

*NEXT

I wish to have the number of loop time is feasible........

%input% is user input number....

My question is can I have 0,1,2,3,4,5 become user input variable.

Meaning

if user input is 6 then I will have %PeriodLoop2%=0,1,2,3,4,5

if user input is 9 then I will have %PeriodLoop2%=0,1,2,3,4,5,6,7,8

if user input is 12 then I will have %PeriodLoop2%=0,1,2,3,4,5,6,7,8,9,10,11

IS it posibble to do this???

[Former Member · Accepted solution]

Yes it is possible to do this, but you have to make some descisions regarding how your time dimension is set up. (I assume 1,2,3,4,5 stand for monthnumbers?)

First of all, you want to be able for the user to input the number? This seems quite hard, and maybe it is better to force the user to select a time dimension member. This way, if the user selects this time dimension member, you can use this member in the logic.

I will tell you how.

To enable the user to select the time dimension, you can use the following statement in the modifyscript for the package:

PROMPT(SELECTINPUT,,,,%TIME_DIM%)

This selection is automatically saved into a selection file by BPC, which can be used in logic statements if you reference it in the modifyscript.

TASK(LOGIC,SELECTIONFILE,%SELECTIONFILE%)

Now, the selected month is stored in the selectionfile, and in the logic, it can be used by referencing the variable %TIME_SET%.

No I will provide an example of the scriptlogic you could use.

First we select the year in which the user wants to run the logic (I assume that if a user selects march 2010, he will only want to run the logic for 2010.JAN and 2010.FEB). And we select the monthnumber of the selected month, to perform a selection on those to variables.

*SELECT(%YEAR%,"YEAR","TIME","ID='%TIME_SET%')
*SELECT(%MONTHNUM%,"MONTHNUM","TIME","ID='%TIME_SET%)

After that, we will select the months previous to the month selected, but in the same year. This will be done with this query:

*SELECT(%PREVIOUSMONTHS%,"ID","TIME","ID in (SELECT ID FROM mbrTime where Monthnum < '%TIME_SET%' AND YEAR='%YEAR%' and CALC='N')

Then we have al periods in the %PREVIOUSMONTHS% variable.

Now we can start the loop

*FOR %PeriodLoop2%=%PREVIOUSMONTHS%
*WHEN *
*IS *
*REC(EXPRESSION=GET(time=Next(%PeriodLoop2%)),category=%PlanCategory%,PlanItem= #RP420004accl,time=Next(%TIME_SET%))
*ENDWHEN
*NEXT

Hopefully I understood your problem correctly.

Kind Regards,

Tim Vierhout

[Former Member]

Hi Tim Vierhout ,

Thanks for your time to guide me...

What I want achieve is I want to calc 2010.Jan budget.

Then I will need to accumulate last 6 (maybe 3,9,12) month actual value

which is from 2009.Jul to 2009.DEC.

with the accumulate i will multiple by a % then I may divide by number of month(6) which is user input

then store in 2010.jan budget..

my script

*when time

*is 2009.JUL

*FOR %PeriodLoop2%=0,1,2,3,4,5

*REC(EXPRESSION=GET(time=Next(%PeriodLoop2%)),category=%PlanCategory%,PlanItem= #RP420004accl,time=Next(%input%))

*NEXT

ENDWHEN

This script is only temp store the accumulate value. I have another script to calc real budget...

actualy wat I want is 2010.JAN budeget

but I cannot start with

*when time

*is 2010.JAN

because there is no figure for 2010.JAN, there is no trigger point.

Hope you can understand...

[Former Member]

Hi Tim,

I think now really understand my question.

Yes, User are feasible to choose how many month to do the calculation...3 month, 6 month, 9 month, or 12 month..

Is it possible to do this ??

Thanks...

[Former Member]

Maybe it is an idea to let the user select multiple time periods in the prompt? You can then loop the %TIME_SET% variable, without any additional statements, and the logic will run. However I think you need to remove the NEXT() statements from the REC expression, but I am not completly certain on what you want to achieve with this, so this you could check yourself.

[Former Member]

Hi,

The reason I use NEXT because i may have situation i need to loop period on 2 year

example i may need period:

2009.MAR, 2009.FEB, 2009.JAN, 2008.DEC, 2008.NOV, 2008.OCT

IF I using your method , i will suffer that i only can get time period in one year only,

which i can not loop period cross year....

Thanks.....

[Former Member]

Do you always need the same amount of previous months? Or does this differ? For example, does the user needs to be able to select how many months there are needed?

[Former Member]

HI Tim Vierhout,

Thanks for your Info. But

There are few thing I am not understand.

Example

IF user input TIME value with PROMPT(SELECTINPUT,,,,%TIME_DIM%)

let's say 2009.JUL

then

'%TIME_SET% = 2009.JUL

then with the script

*SELECT(%YEAR%,"YEAR","TIME","ID='%TIME_SET%')

*SELECT(%MONTHNUM%,"MONTHNUM","TIME","ID='%TIME_SET%)

then i get

%YEAR%=2009

%MONTHNUM%=07 (I assumme)....

then with the script

*SELECT(%PREVIOUSMONTHS%,"ID","TIME","ID in (SELECT ID FROM mbrTime where Monthnum < ' AND YEAR='%YEAR%' and CALC='N')

I not sure whether statement your " where Monthnum < ' AND YEAR='%YEAR%' " did you missing something

or suppose should be " where Monthnum < '%MONTHNUM%' AND YEAR='%YEAR%' " ....

then i will get

%PREVIOUSMONTHS% = 2009.JUN, 2009.MAY, 2009.APR, 2009.MAR, 2009.FEB, 2009.JAN

then

if i using the script

*FOR %PeriodLoop2%=%PREVIOUSMONTHS% (is this still able to run correctly ??)

because i will get:

*REC(EXPRESSION=GET(time=Next(2009.JUN)),category=%PlanCategory%,PlanItem= #RP420004accl,time=Next(2009.JUL))

This script also will work correctly ???

Please correct me if I am wrong...

Thanks...

[Former Member]

>

>

> then with the script

> *SELECT(%PREVIOUSMONTHS%,"ID","TIME","ID in (SELECT ID FROM mbrTime where Monthnum < ' AND YEAR='%YEAR%' and CALC='N')

quote}

This indeed should be:

*SELECT(%PREVIOUSMONTHS%,"ID","TIME","ID in (SELECT ID FROM mbrTime where Monthnum < %MONTHNUM%' AND YEAR='%YEAR%' and CALC='N')

With the loop, I don't excactly understand why you do this in this way. What do you want to achieve by using the NEXT statement in the loop? Because the loop will automatically retrieve the timeperiods because we specified them in the %PREVIOUSMONTHS% variable.