cancel
Showing results for 
Search instead for 
Did you mean: 

Select statement using mathematical operator on property

cecilia_petersson2
Active Participant
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

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

Answers (4)

Answers (4)

Former Member
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

Hi Mattias,

Please, don't provide incorrect answers without testing

The second argument in SELECT doesn't support expressions, only property name. And the property is a text field, not a number (with the only exception - BASPERIOD).

Vadim

Former Member
0 Kudos

Hi Vadim,

For me it works like a charm, maybe that is because I use BPC for Microsoft, I thought this functionality would be the same in Netweaver.

I am sorry if it doesn't work in Netweaver.

Brgds

Mattias

former_member186338
Active Contributor
0 Kudos

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

cecilia_petersson2
Active Participant
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

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

former_member200327
Active Contributor
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

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!