Division Calculation using Script Logic

[former_member488614]

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.

[former_member186338 · Accepted solution]

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

[former_member488614]

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 

[former_member186338]

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)

...

[former_member488614]

Thanks a lot Vadim...It is working....

I really appreciate your help.

[former_member488614]

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?

[former_member186338]

Search for ternary operator!

[former_member488614]

Thanks Vadim.

[former_member488614]

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.

[former_member186338]

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!