Skip to Content
avatar image
Former Member

Adding different Category values in Script logic

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
data-set1.jpg (169.3 kB)
system-details.jpg (17.2 kB)
ujkt1.jpg (134.6 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Oct 19, 2017 at 06:19 AM

    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
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

      project.jpg (103.8 kB)
  • avatar image
    Former Member
    Oct 19, 2017 at 04:43 PM

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

    Thanks for your valuable suggestions.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 19, 2017 at 02:36 PM

    "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.

    Add comment
    10|10000 characters needed characters exceeded