Skip to Content

Select statement using mathematical operator on property

Hi,

In EHP7 FOR SAP ERP 6.0, JavaScript engine, I have this select statement to retrieve current year:

*SELECT(%ACT_YEAR%, "YEAR","CATEGORY", "[ID] = 'ACTUAL'")


Now I'd like to create a select statement that uses the Year property for Category Actual to find next year, i.e. something like this (not working):

*SELECT(%ACT_YEAR_1%, "YEAR","CATEGORY", "([ID] = 'ACTUAL') +1 ")

What would be the syntax?

Thanks,

Cecilia

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Sep 15, 2014 at 10:30 AM

    Hi Cecilia,

    Sorry, by this is not possible: ([ID] = 'ACTUAL') +1 - only text comparison!


    If you want to get a next year, you have to create a new property of CATEGORY dimension like NEXTYEAR and fill it with the required number.


    Vadim

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 15, 2014 at 10:51 AM

    For the fixed list of years (can be long enough) you can also use the following trick to avoid property creation:

    *SELECT(%ACT_YEAR%,"[YEAR]",PLANTYPE,"[ID]=ACTUAL")

    *SELECT(%Y2010%,"[YEAR]",PERIODS,"[ID]=2011.JAN")

    *SELECT(%Y2011%,"[YEAR]",PERIODS,"[ID]=2012.JAN")

    *SELECT(%Y2012%,"[YEAR]",PERIODS,"[ID]=2013.JAN")

    *SELECT(%Y2013%,"[YEAR]",PERIODS,"[ID]=2014.JAN")

    *SELECT(%Y2014%,"[YEAR]",PERIODS,"[ID]=2015.JAN")

    *SELECT(%Y2015%,"[YEAR]",PERIODS,"[ID]=2016.JAN")

    *SELECT(%Y2016%,"[YEAR]",PERIODS,"[ID]=2017.JAN")

    *SELECT(%Y2017%,"[YEAR]",PERIODS,"[ID]=2018.JAN")

    *SELECT(%Y2018%,"[YEAR]",PERIODS,"[ID]=2019.JAN")

    *SELECT(%Y2019%,"[YEAR]",PERIODS,"[ID]=2020.JAN")

    *SELECT(%Y2020%,"[YEAR]",PERIODS,"[ID]=2021.JAN")

    *SELECT(%Y2021%,"[YEAR]",PERIODS,"[ID]=2022.JAN")

    *SELECT(%Y2022%,"[YEAR]",PERIODS,"[ID]=2023.JAN")

    *SELECT(%NEXT_YEAR%,"[YEAR]",PERIODS,"[ID]=%Y%ACT_YEAR%%.JAN")

    *XDIM_MEMBERSET PERIODS=%NEXT_YEAR%.JAN

    Look on text in bold - double substitution of variables

    Vadim

    P.S. In my system CATEGORY=PLANTYPE; TIME=PERIODS, instead of ".JAN" you can use ".01"

    Message was edited by: Vadim Kalinin - P.S. added!

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 16, 2014 at 12:25 AM

    Hi Cecilia,

    You can try using TMVL in time Dimension.

    First, select all members that have YEAR = %ACT_YEAR% into variable %FULL_YEAR%  (I hope you have that Property in TIME Dimension). Then use TMVL(%FULL_YEAR%, 1). This will brings first member of next year. Finally, you can get property YEAR of that member.

    Hope this idea helps.

    Gersh

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Gersh,

      The issue is that you can't use *SELECT on the member you get using TMVL (SELECT is processed before TMVL).

      To get all members of the next year (in the scope) the following code can be used:

      *SELECT(%ACT_YEAR%,"[YEAR]",PLANTYPE,"[ID]=ACTUAL")

      *XDIM_MEMBERSET PERIODS =TMVL(12,%ACT_YEAR%.JAN),TMVL(13,%ACT_YEAR%.JAN),TMVL(14,%ACT_YEAR%.JAN),TMVL(15,%ACT_YEAR%.JAN)

      B.R. Vadim

  • Sep 19, 2014 at 11:24 AM

    Thanks Vadim!

    As it's only a matter of one additional property, I'll go for thas solution in this case, but your other suggestions may well come in handy in other cases!

    /Cecilia

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Cecilia,

      From another point of view this extra code with double substitution is simple and working fine. And you will not need to maintain extra property 😊

      Vadim

  • avatar image
    Former Member
    Sep 22, 2014 at 09:03 PM

    Dear Cecilia,

    I would say this is the solution

    *SELECT(%ACT_YEAR_1%, "[YEAR]+1", "CATEGORY", "[ID] = 'ACTUAL'")


    the ID you look for is still ACTUAL and you will return whatever figure you have in year and add 1 to that returned figure.

    Brgds

    Mattias

    Add comment
    10|10000 characters needed characters exceeded

    • Yes Mattias,

      Unfortunately, script logic is 80% different between MS and NW. In NW it's much more limited and you have to use very specific tricks to implement even a simple requirement.

      B.R. Vadim