multiplication of account using script logic

[Former Member]

Hi all,

My requirement is multiply the head count to salary account from specific time period.

For example user will input salary only for one month like 2015.jan now this salary account value should be multiplied with head count account for each time period of that year, and result should be stored on third account say headcount_exp.

*LOOKUP FINANCE

*DIM CATEGORY="ACTUAL"

*DIM DATASRC="INPUT"

*DIM TIME="2006.JAN"

*DIM DEPARTMENT="NODEPT"

*DIM ENTITY="IND"

*DIM INTCO="I_IND"

*DIM PRODLINE="P0101"

*DIM ACCOUNT="INVESTEQUITY"

*DIM DTTIM:RPTCURRENCY="LC"

*ENDLOOKUP

*XDIM_MEMBERSET CATEGORY=ACTUAL

*XDIM_MEMBERSET DATASRC=INPUT

*XDIM_MEMBERSET DEPARTMENT=NoDept

*XDIM_MEMBERSET ENTITY=IND

*XDIM_MEMBERSET INTCO=I_IND

*XDIM_MEMBERSET PRODLINE=P0101

*XDIM_MEMBERSET RPTCURRENCY=LC

*XDIM_MEMBERSET RPTCURRENCY=LC

*WHEN ACCOUNT

*IS SALESUNITS

   *REC(FACTOR=LOOKUP(DTTIM),ACCOUNT=IICREV)   

*ENDWHEN

I have hard coded time value in look up but i want time member to be pass from data prompt.

OR

Any other solution for above script?

Regards,

Sushant.

[cecilia_petersson2 · Accepted solution]

Hi Sushant,

You can dynamically refer to January like this:

*REC(EXPRESSION=%VALUE%*GET(ACCOUNT="SALARY",TIME="%YEAR%.JAN"),ACCOUNT=HEADCOUNT_EXP)

/Cecilia

[Former Member · Accepted solution]

Hi Cecilia,

Thanks for your prompt reply.

Your solution works for me but %year% will take current year of system. I want to pass year value to my script and that should be depend on user selection.

I have used allocation script it works fine  but only when I hard code the time value. I want to make it dynamic.

*XDIM_MEMBERSET CATEGORY=ACTUAL

*XDIM_MEMBERSET DATASRC=INPUT

*XDIM_MEMBERSET DEPARTMENT=NoDept

*XDIM_MEMBERSET ENTITY=IND

*XDIM_MEMBERSET INTCO=I_IND

*XDIM_MEMBERSET PRODLINE=P0101

*XDIM_MEMBERSET RPTCURRENCY=LC

*RUNALLOCATION

   *FACTOR=USING

   *DIM ACCOUNT WHAT=SALESUNITS; WHERE=IICREV; USING=INVESTEQUITY

   *DIM TIME WHAT=BAS(2006.TOTAL); WHERE=<<<; USING=2006.JAN

*ENDALLOCATION

Now i am trying to achieve this using SELECT statement and using FOR NEXT statement, It work when hard code the value in the select statement.

I have one question.

Can we use %TIME_SET% statement in select statement?  

//*SELECT(%VAR%,"YEAR",TIME,"ID='2006.JAN'")

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

*XDIM_MEMBERSET CATEGORY=ACTUAL

*XDIM_MEMBERSET DATASRC=INPUT

*XDIM_MEMBERSET DEPARTMENT=NoDept

*XDIM_MEMBERSET ENTITY=IND

*XDIM_MEMBERSET INTCO=I_IND

*XDIM_MEMBERSET PRODLINE=P0101

*XDIM_MEMBERSET RPTCURRENCY=LC

*FOR %VAR1%=%VAR%

*FOR %VAR2%=TOTAL

*RUNALLOCATION

   *FACTOR=USING

   *DIM ACCOUNT WHAT=SALESUNITS; WHERE=IICREV; USING=INVESTEQUITY

   *DIM TIME WHAT=BAS(%VAR1%.%VAR2%); WHERE=<<<; USING=%VAR1%.JAN

*ENDALLOCATION

*NEXT

*NEXT

Regards,

Sushant.

[cecilia_petersson2 · Accepted solution]

Hi Sushant,

Sorry for the delay in getting back to you. Try this setup:

Note: I'm adding [PERIOD]='JAN' to the *SELECT statement to ensure only one record is selected as the script doesn't seem to be able to handle several hits for *SELECT. It could be any property as long as you only get one hit

/Cecilia

[Former Member · Accepted solution]

Hi,

Thanks again for your prompt reply.

I have already implemented the solution  almost in similar way. But i have one question can we use "AND","OR" condition in the select statement? Because somewhere i read that we can not use AND OR statements in the select statement and tried it but no result.

[cecilia_petersson2 · Accepted solution]

Glad it's solved! Both AND and OR are working fine for me in *SELECT. Maybe it wasn't so in earlier versions. I'm on admin client 10.0.16.0.

/Cecilia

[Former Member]

Hi Sushant,

I do not  understand why you have used LOOKUP function to get the value.....Is it from different model??

But as per your requirement if your running the script through Data manager package and passing the time, then simply use

*XDIM_MEMBERSET TIME = %TIME_SET% and then ur logic with REC statement would be

*WHEN ACCOUNT

*IS HEADCOUNT

   *REC(EXPRESSION=%VALUE%*[ACCOUNT].[SALARY],ACCOUNT=HEADCOUNT_EXP)  

*ENDWHEN

If not Data manger package use this script in the default logic.

Regards,

JP

[Former Member]

Hi JP,

Thanks for your prompt reply.

It is from same model not from different model. But requirement is different. Salary account value will be stored only  in Jan of each year and head count will be for each month now i have to calculated head count expenses for each month.

salary of Jan * head count of  particular month.

[Former Member]

Ok...In that case Sushant, you can simply use excel formula like =E1(value of Jan's Salary) for other respective months and copy the value into that months. Then as per my above reply, write the logic with WHEN/ENDWHEN. Call this logic through default logic.

Regards,

JP