Copy Value using Logic Script

[Former Member]

Hi All,

I have scenario whereby the value in the period need to be replicated to the next periods until period 12. For example,

	Source Data	Target Data
Period 1	1000	1000
Period 2	1500	=1000+1500
Period 3	3000	=1000+1500+3000
Period 4	4000	=1000+1500+3000+4000
Period 5	4500	=1000+1500+3000+4500
Period 6	0	=1000+1500+3000+4500
Period 7	0	=1000+1500+3000+4500
Period 8	0	=1000+1500+3000+4500
Period 9	5000	=1000+1500+3000+4500+5000
Period 10	0	=1000+1500+3000+4500+5000
Period 11	0	=1000+1500+3000+4500+5000
Period 12	0	=1000+1500+3000+4500+5000

So if there is value in period 1, it will be replicated in the next periods (2,3,4...)until period 12,

If there is value in period 2, it will be replicated in the next periods (3,4,5....) until period 12, and etc

How do I script the logic if the logic need to be called in the data package?

I am thinking to use the allocation, but not sure how to create a dynamic variable to tell the system if TIME=2016.01  then WHAT is 2016.01 and WHERE is 2016.02 onwards, if TIME=2016.02 then WHAT is 2016.02 and WHERE is 2016.03 onward.

*RUNALLOCATION

*FACTOR = 1

*DIM TIME WHAT = 2016.01 ;WHERE = 2016.02; Using = <<<

*ENDALLOCATION

Appreciate your help.

Thanks

[former_member186338 · Accepted solution]

Hi,

Please, read

If you want to run a DM package - what parameters the user have to enter? Etc...

From the other side your Target data looks like YTD of periodic Source...

Vadim

[former_member186338 · Accepted solution]

P.S. Is it PERIODIC -> YTD conversion for the whole Year???

[Former Member · Accepted solution]

Hi..

The scenario is used to duplicate the budget sAlary of Employee monthly. if new employee will be employed On January with salary = USD1.000 /month then User will enter USD1.000  in the budget salary On January and system has to auto duplicate the salary on the next months onward (so user does not need to enter the budget salary every month)

Let say in February ,another new employed will be employed (starting the job in february) with salary= USD 1.500 /month, then there will be Additional budget USD 1500 in February (entered by users) and system has to duplicate the salary on the next months onward .

User only enter entity and category in the data package selection.

From the category we can derive the budget year in the script .

We are in BPC 10.1 . Thanks

[former_member186338 · Accepted solution]

Let's check:

The user can enter value for any month of the year (and in multiple months of the year also). Data is saved.

Then user will launch DM package selecting entity and category.

Category member will contain valid YEAR property with the Year to perform calculations.

Is it correct?

Vadim

P.S. Do you want to update the same members or it's better to use different AUDITID for target?

[Former Member · Accepted solution]

yes,correct.

below is  how the data input form looks like

employee	Time: 2016.01	Time: 2016-02	2016.03 Until 2016.11	T ime: 2016. 12
New manager	1000	1500 ( additional Salary for  new manager hired on Feb)
New driver	200			250 (additional salary for new driver hired on Dec)

thanks

[Former Member · Accepted solution]

Audit iD is not required, thanks

[former_member186338 · Accepted solution]

"Audit iD is not required"

How  the values will be entered?

Do you have a list of dummy ID's for new positions? Or what?

What if you want to increase salary of some employee? How you will enter this data?

What if you want to terminate some employee???

Sorry, but you have to explain the data entry logic step by step for all possible cases...

If you enter and save some value in the input form it will overwrite the previous value!

Vadim

[Former Member · Accepted solution]

We have ID for each position. We budget payroll per position(not by individual employee)

We do not consider the termination , The increment of salary is calculated  in different account after we duplicate the salary cost each month ,This scenario is to handle budget for existing staff (already hired), what we are discussing now is scenario for new Staff. in my case, existing and new staff are separate data set.

actually..the user enter number of staff in account =quantity and then system will calculate the USD value (account: salary cost)in the month where Quantity is entered . from here, i will need to duplicate the USD Value as per what i explained earlier.

If the users change the quantity in the input form then DM need to be re executed and the previous value Should be replaced.

any idea how to script it ?

.

[Former Member · Accepted solution]

actually l just want to ask on how to pass the time parameter dynamically in the Script below :

When time:20i6. 01

*RUNALLOCATION

*FACTOR = 1

*DIM TIME WHAT = 2016.01 ;WHERE = 2016.02 Until 2016.12; Using = <<<

*ENDALLOCATION

when time:2016.02

*RUNALLOCATION

*FACTOR = 1

*DIM TIME WHAT = 2016.02 ;WHERE = 2016.03 until 2016.12; Using = <<<

*ENDALLOCATION

when time:2016.03

*RUNALLOCATION

*FACTOR = 1

*DIM TIME WHAT = 2016.03 ;WHERE = 2016.03 until 2016.12; Using = <<<

*ENDALLOCATION

and so on until when time: 2016.11

Of course I cannot just simply use the above script ,but I just want to get idea on how to pass the year property in category dimension and to pass the period dynamically in above script,

thanks

[former_member186338 · Accepted solution]

Sorry, but the script is completely wrong and will never do what you think! Each *RUNALLOCATION will overwrite target...

About dynamic parameters - your dynamic parameter is YEAR:

*SELECT(%Y%,[YEAR],CATEGORY,[ID]=%CATEGORY_SET%)

Then you can use %Y% to generate dynamic month members like:

%Y%.01, %Y%.02,...

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thank you for the reply.

If run allocation does not work for this scenario... what could be a way to do it?  is it practical to create many *REC formula under *WHEN?

[former_member186338 · Accepted solution]

To do what? I do not see the working input scenario...

You have to separate user input - source and target by some dimension like AUDITID or CATEGORY or...

Then it will be possible to write script.

Vadim

[Former Member · Accepted solution]

Let's assume if we separate the user input by audit iD... what will be the script ?

Thanks

[former_member186338 · Accepted solution]

12 WHEN/ENDWHEN for 12 months with correct scope like:

//For DEC:

*XDIM_MEMBERSET TIME=%Y%.01,...,%Y%.12

*XDIM_MEMBERSET AUDITID=INPUT

*WHEN TIME

*IS*

*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,AUDITID=CALC)

*ENDWHEN

...

Very simple script...

Vadim

[Former Member · Accepted solution]

It works.

Thank you Vadim.

[Former Member]

Hi

I am not sure of your need to achieve this requirement via script logic only. If you are looking for other alternatives, may be you see below alternative.

Using input schedule (worksheet):

Modify the input schedule where user will input details of new recruits, like below and save data to BPC.

In this case, your whole process will be completed in 1 step, instead of 2 steps in existing process, where the user has to run script logic separately.

Formula to use: =IF($C5<=F$3,$D5,0)

Thank you.

[former_member186338]

The requirement was to have multiple joins per each position...

[Former Member]

Thank you Vadim, I missed to notice that part.

Achieving this via input template becomes complex for the users.