Hello all,
I have a scenario, please check attachment records-to-be-generated.jpg
I am trying to calculate division using script logic and the below logic returns '0' for ([COSTELEMENT].[YRS_PT18])
*WHEN CATEGORY *IS FCST_Q2 *WHEN PROJECT.PROJTYPE *IS PT18 *REC(EXPRESSION=%VALUE%/([COSTELEMENT].[YRS_PT18]),COSTELEMENT=850000,DATASRC=CALC,PO=NO_PO) *ENDWHEN *ENDWHEN
Please let me know if this approach is OK or is there any other easy solution to get the required result set.
Thank you in advance.
Correct scoping and correct tuple required:
*DIM_MEMBERSET CATEGORY=FCST_Q2 //or %CATEGORY_SET% *DIM_MEMBERSET PROJECT <> NO_PROJECT *DIM_MEMBERSET COSTELEMENT=620000 *DIM_MEMBERSET DATASOURCE=INPUT *DIM_MEMBERSET PO=NO_PO *DIM_MEMBERSET TIME=2017.01 //or... *WHEN PROJECT.PROJTYPE *IS PT18 *REC(EXPRESSION=%VALUE%/([COSTELEMENT].[YRS_PT18],[TIME].[2017.AINP],[PROJECT].[NO_PROJECT]),COSTELEMENT=850000,DATASRC=CALC) *ENDWHEN
Or even:
*SELECT(%PRJPT18%,[ID],PROJECT,PROJTYPE=PT18) *DIM_MEMBERSET CATEGORY=FCST_Q2 //or %CATEGORY_SET% *DIM_MEMBERSET PROJECT=%PRJPT18% *DIM_MEMBERSET COSTELEMENT=620000 *DIM_MEMBERSET DATASOURCE=INPUT *DIM_MEMBERSET PO=NO_PO *DIM_MEMBERSET TIME=2017.01 //or... *WHEN PROJECT *IS * *REC(EXPRESSION=%VALUE%/([COSTELEMENT].[YRS_PT18],[TIME].[2017.AINP],[PROJECT].[NO_PROJECT]),COSTELEMENT=850000,DATASRC=CALC) *ENDWHEN
Hello Vadim-Please find the details here.
System Details-
K2 calculation engine-JAVASCRIPT
Dimensions-
PROJECT
PROJTYPE is property to Project Dimension
CATEGORY
COSTELEMENT
YRS_PT18 is one of the members in COSTELEMENT Dimension
DATASOURCE
TIME
PO
Purpouse of this script: To calculate Depreciation amount and this should be launched by DM package by selecting Category input, I will add variable later.
UJKT validation ujkt.jpg
Records are generating since it is returning '0' value for ([COSTELEMENT].[YRS_PT18]),overall sign data becoming '0'.
Please let me know if you need any other information.
Thanks in Advance.
Hi Vadim-The below code is working fine, but can we make [TIME].[2017.AINP] as dynamic? because this value will change based on Category selection, user is going to select always Category only.
See attached possible data set data-set.jpg
*SELECT(%PRJPT18%,[ID],PROJECT,PROJTYPE=PT18) *XDIM_MEMBERSET CATEGORY=FCST_Q2_05 // %CATEGORY_SET% *XDIM_MEMBERSET PROJECT=%PRJPT18% *WHEN PROJECT *IS * *REC(EXPRESSION=%VALUE%/([COSTELEMENT].[YRS_PT18],[TIME].[2017.AINP],[PROJECT].[NO_PROJECT]),COSTELEMENT=710004,DATASRC=CALC,PO=NO_PO) *ENDWHEN
Actually this is another question!
In the CATEGORY dimension create required property: TIMEPER with
FCST_Q2_05 2017.AINP
FCST_Q3_05 2018.AINP
...
Then instead of tuple ([COSTELEMENT].[YRS_PT18],[TIME].[2017.AINP],[PROJECT].[NO_PROJECT])
use LOOKUP:
//definition in the beginning of script
*LOOKUP RATE
*DIM COSTELEMENT="YRS_PT18"
*DIM PROJECT="NO_PROJECT"
*DIM TTT:TIME=CATEGORY.TIMEPER
*ENDLOOKUP
...
*REC(EXPRESSION=%VALUE%/LOOKUP(TTT),COSTELEMENT=850000,DATASRC=CALC)
...
Hello Vadim
one quick question, how do we eliminate division by zero or blank in this scenario?
Ex-If LOOKUP(TTT) is '0' or Blank I don't want to do the calculation to avoid error.Any Idea?
Search for ternary operator!
Thanks Vadim.
Thanks a lot Vadim...It is working....
I really appreciate your help.
First of all, please read https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/
I don't want to spend time asking each question individually!
P.S. Yes, it can be done using script, but your script is incorrect!