Question Re: multiplication of account using script logic in Financial Management Q&A

multiplication of account using script logic

Former Member — Wed, 27 May 2015 11:50:01 GMT

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

*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.

Any other solution for above script?

Regards,

Sushant.

Re: multiplication of account using script logic

Former Member — Wed, 27 May 2015 12:23:44 GMT

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,

Re: multiplication of account using script logic

Former Member — Wed, 27 May 2015 12:53:50 GMT

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.

Re: multiplication of account using script logic

Former Member — Wed, 27 May 2015 13:14:56 GMT

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,

Re: multiplication of account using script logic

cecilia_petersson2 — Wed, 27 May 2015 15:52:23 GMT

Hi Sushant,

You can dynamically refer to January like this:

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

/Cecilia

Re: multiplication of account using script logic

Former Member — Thu, 28 May 2015 07:44:00 GMT

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

Regards,

Sushant.

Re: multiplication of account using script logic

cecilia_petersson2 — Wed, 03 Jun 2015 10:41:46 GMT

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

Re: multiplication of account using script logic

Former Member — Wed, 03 Jun 2015 11:02:47 GMT

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.

Re: multiplication of account using script logic

cecilia_petersson2 — Wed, 03 Jun 2015 11:21:11 GMT

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