0

# Division Calculation using Script Logic

Oct 16, 2017 at 11:32 PM

86

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.

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
Former Member Oct 17, 2017 at 02:22 PM
0

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.

system-details.jpg (17.2 kB)
ujkt.jpg (85.0 kB)
Share
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

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 Member

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 Member

Search for ternary operator!

Former Member

Former Member

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

I really appreciate your help.

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