on 09-15-2014 10:55 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.