Skip to Content
0

Division Calculation using Script Logic

Oct 16, 2017 at 11:32 PM

86

avatar image
Former Member

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Vadim Kalinin Oct 17, 2017 at 03:38 PM
0

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
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Oct 17, 2017 at 02:22 PM
0

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.


system-details.jpg (17.2 kB)
ujkt.jpg (85.0 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Oct 17, 2017 at 05:17 PM
0

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 

data-set.jpg (33.6 kB)
Show 5 Share
10 |10000 characters needed characters left characters exceeded

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)

...

0
Former Member
Vadim Kalinin

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?

0

Search for ternary operator!

0
Former Member
Vadim Kalinin

Thanks Vadim.

0
Former Member

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

I really appreciate your help.

0
Vadim Kalinin Oct 17, 2017 at 07:59 AM
0

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!

Share
10 |10000 characters needed characters left characters exceeded