on 05-27-2015 12:50 PM
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.
Hi Sushant,
You can dynamically refer to January like this:
*REC(EXPRESSION=%VALUE%*GET(ACCOUNT="SALARY",TIME="%YEAR%.JAN"),ACCOUNT=HEADCOUNT_EXP)
/Cecilia
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
13 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.