Adding different Category values in Script logic

[former_member488614]

Hello all, I have a requirement to add two different category values and post into one category based on a time range.

Scenario screenshot

K2 calculation engine-JAVASCRIPT

Dimensions-

PROJECT

CATEGORY

COSTELEMENT

DATASOURCE

TIME

PO

Purpose of this script: To calculate Depreciation amount and this should be launched by DM package by selecting Category input, I will add variable later.

System Details

UJKT validation-

My Script-Looks like there is an issue at TIME dim at *REC(..TIME=%INSER_MNTH%

Also how to avoid specific time members in the rage?

Ex:- in my ACTUAL category TIME range between 2014.01 to 2017.02 I have 2014.LTD,2014.AINP,2014.Q1 etc... members, I don't want to add these member values in the destination amount.

*SELECT(%ACT_MNTH%,ID,TIME,[ID]>=2014.01 AND [ID]<%ST_PERIOD%)

In the Above Select, I want only 2014.01 to 2014.12+2015.01 to 2015.12+2016.01 to 2016.12+2017.01 to 2017.02 etc.. in the range and should be excluded 2014.LTD,2014.AINP,2014.Q1 etc... members.

Any suggestions? Thanks in advance.

*SELECT(%PRJPT31%,[ID],PROJECT,PROJTYPE=PT31)
*SELECT(%INSERDT%,INSER_MNTH_YR,PROJECT,[ID]=%PRJPT31%)

*SELECT(%ACT_CAT%,ACTUAL_CATEGORY,CATEGORY,[ID]=FCST_Q2_05) 
*SELECT(%ST_PERIOD%,START_PERIOD,CATEGORY,[ID]=FCST_Q2_05)
*SELECT(%END_PERIOD%,LAST_PERIOD,CATEGORY,[ID]=FCST_Q2_05)
*SELECT(%CAT_MNTH%,ID,TIME,[ID]>=%ST_PERIOD% AND [ID]<=%END_PERIOD%)
*SELECT(%ACT_MNTH%,ID,TIME,[ID]>=2014.01 AND [ID]<%ST_PERIOD%)
*SELECT(%INSER_MNTH%,ID,TIME,[ID]>=%INSERDT% AND [ID]<=%END_PERIOD%)

*XDIM_MEMBERSET CATEGORY=%ACT_CAT%,FCST_Q2_05
*XDIM_MEMBERSET TIME=%ACT_MNTH%,%CAT_MNTH%
*XDIM_MEMBERSET PROJECT=%PRJPT31%

*WHEN CATEGORY
*IS *
*WHEN TIME
*IS *
*WHEN PROJECT
*IS *
*REC(EXPRESSION=%VALUE%/12,CATEGORY=FCST_Q2_05,TIME=%INSER_MNTH%,COSTELEMENT=710004,DATASRC=CALC,PO=NO_PO)
*ENDWHEN
*ENDWHEN
*ENDWHEN

[former_member186338 · Accepted solution]

Instead of TIME member ID use TIMEID property (hope it's correctly maintained - only for base members and AINP has different range):

*SELECT(%PRJPT31%,[ID],PROJECT,PROJTYPE=PT31) //hope you have only single PROJECT with PROJTYPE=PT31
*SELECT(%INSERDT%,INSER_MNTH_YR,PROJECT,[ID]=%PRJPT31%) //single value!

*SELECT(%ACT_CAT%,ACTUAL_CATEGORY,CATEGORY,[ID]=FCST_Q2_05) 
*SELECT(%ST_PERIOD%,START_PERIOD,CATEGORY,[ID]=FCST_Q2_05)
*SELECT(%END_PERIOD%,LAST_PERIOD,CATEGORY,[ID]=FCST_Q2_05)

*SELECT(%TID_INSERDT%,TIMEID,TIME,[ID]=%INSERDT%)
*SELECT(%TID_ST_PERIOD%,TIMEID,TIME,[ID]=%ST_PERIOD%)
*SELECT(%TID_END_PERIOD%,TIMEID,TIME,[ID]=%END_PERIOD%)

*SELECT(%CAT_MNTH%,ID,TIME,[TIMEID]>=%TID_ST_PERIOD% AND [TIMEID]<=%TID_END_PERIOD%)
*SELECT(%ACT_MNTH%,ID,TIME,[TIMEID]>=20140100 AND [TIMEID]<%TID_ST_PERIOD%)
*SELECT(%INSER_MNTH%,ID,TIME,[TIMEID]>=%TID_INSERDT% AND [TIMEID]<=%TID_END_PERIOD%)

But the rest of your script is incorrect:

*WHEN CATEGORY
*IS *
*WHEN TIME
*IS *
*WHEN PROJECT
*IS *
*REC(EXPRESSION=%VALUE%/12,CATEGORY=FCST_Q2_05,TIME=%INSER_MNTH%,COSTELEMENT=710004,DATASRC=CALC,PO=NO_PO)
*ENDWHEN
*ENDWHEN
*ENDWHEN

First - only one *WHEN is nesessary.

Second - in REC only single TIME member is allowed:

TIME=%INSER_MNTH% - multiple members!!

You have to use FOR/NEXT

*WHEN CATEGORY
*IS *
*FOR %T%=%INSER_MNTH%
*REC(EXPRESSION=%VALUE%/12,CATEGORY=FCST_Q2_05,TIME=%T%,COSTELEMENT=710004,DATASRC=CALC,PO=NO_PO)
*NEXT
*ENDWHEN

[former_member488614 · Accepted solution]

Hi Vadim-Thanks for the quick reply and yes, we are maintaining TIMEID property properly, member values I see as 20180600 for 2018.06 as you suggested I will change the code for TIME and check.

And for Project, I have multiple PROJECTs with PROJTYPE=PT31 and each PROJECT has different INSER_MNTH_YR.

See attached example data set. project.jpg

How to handle this scenario? Thanks in advance.

[former_member488614]

Ok Vadim, then I will try with Custom logic BadI.

Thanks for your valuable suggestions.

[former_member186338]

"And for Project, I have multiple PROJECTs with PROJTYPE=PT31 and each PROJECT has different INSER_MNTH_YR."

In this case simply forget about script logic and write Custom Logic badi.

It's not possible to use SELECT inside FOR/NEXT loop having variable in FOR as a criteria for SELECT.

Depreciation calculation are traditionally programmed as custom logic badi.

P.S. Sorry, that I missed the word "depreciation" in the original question.