cancel
Showing results for 
Search instead for 
Did you mean: 

Logic script - Current year and next year

0 Kudos

Hello,

I am working on a script to copy the data to forecast versions. Forecast is monthly so copy should work for open periods of current year and next year. Current year = 2018 and next year is 2019

I am able to read the open months of the current year however select statement to read next year values is not giving any result

//Select statement to retrieve the current Forecast period of the year, from the property CURR_PER and member ID FCAST_INPUT, value set is 2018.06

*SELECT(%CURR_PER%,"[CURR_PER]",VERSION,"[ID]"='FCAST_INPUT')

*SELECT(%T_CURR_PER%,[ID],TIME,"[ID]"=%CURR_PER%) // Time ID linked to Forecast Time period

*SELECT(%T_CURR_YR%,[YEAR],TIME,"[ID]"=%T_CURR_PER%) // to get current Year

*SELECT(%T_CURR_YR_FCST_PER%,[ID],TIME,"[YEAR]"=%T_CURR_YR% AND "[CALC]" = 'N' AND "[ID]" > %T_CURR_PER%) // to read current year open forecast periods

all the above statement working as expected

I have below select statement to read Next year periods -

*SELECT(%T_NEXT_YR_PER%,[ID],TIME,"[YEAR]"=%T_CURR_YR%+1 AND "[CALC]" = 'N')

This statement is not being read at all ...is this a valid statement?

I am using the variable in Time as

*XDIM_MEMBERSET TIME = %T_CURR_YR_FCST_PER%, %T_NEXT_YR_PER%

In the result I see 2018.07,2018.08,2018.09,2018.10,2018.11,2018.12 only, nothing from the year 2019

Please advice

Accepted Solutions (0)

Answers (3)

Answers (3)

0 Kudos

I am trying to use Calc 'N' for the YEAR selection and use TMVL parameter to derive next year, will get back to you If i hit a road block

thank you as always!

former_member186338
Active Contributor
0 Kudos

Have to repeat - TMVL is not an option in SELECT. SELECT is processed before TMVL processing.

0 Kudos

I am not using TMVL in select statement, instead using that in When statement 🙂

I will share my code once I test it.

Thank you

former_member186338
Active Contributor
0 Kudos

Looks like you don't want to analyze code that I have provided in the link. Using this approach you can find previous year, next year etc...

0 Kudos

Hi Vadim,

Thanks for the input, I have checked other answers however in my case the Time parameter is not input selection from DM,

and I do not have a property in Time dimension to filter the forecast years Current year and Next year

so here is my idea to derive Next year.

I have a property in Version dimension CURR_PER and against member ID FCAST_INPUT, value set is 2018.06, this value is dynamic and gets updated every month by admin e.g. 2018.01, 2018.02 ...2018.12 for the current year and when year changes the values will start from 2019.01, 2019.02 so on and so forth

I would want to do all the below in select statements:

using the value = 2018.06, I would want to use TMVL 12 of 2018.06 will bring me next year value e.g. 2019.06

use the value to derive the "Year" of the value 2019.06 which is 2019

use Year value to get Total which is 2019.TOTAL in a select statement

use Base of 2019.TOTAL to get all the months of 2019

below is my attempt:

//statement to retrieve the current Forecast period of the year, from the property

*SELECT(%CURR_PER%,"[CURR_PER]",VERSION,"[ID]"='FCAST_INPUT')

// Time ID linked to Forecast Time period

*SELECT(%T_CURR_PER%,[ID],TIME,"[ID]"=%CURR_PER%)

//statement to get next year period

*SELECT(%NXT_YR_PER%,"[ID]",TIME,"[ID]"=TMVL(12,%T_CURR_PER%) = Is this valid statement?

// statement to get the Year

*SELECT(%NXT_YR%,[YEAR],TIME,"[ID]"=%NEXT_YR_PER%)

// statement to get the Total of the year

*SELECT(%YRTOTAL%,[ID],TIME,"[YEAR]=%NXT_YR% AND [PERIOD]=TOTAL")

// statement to get the base of year total

SELECT(%NXT_YR_BAS%,[ID],TIME,"[ID]=BAS(%YRTOTAL%) = Is this valid statement?

*XDIM_MEMBERSET TIME = %NXT_YR_BAS%, %T_CURR_PER%

...

...

Inputs please

former_member186338
Active Contributor
0 Kudos

Sorry, but your idea is incorrect! TMVL can't be used in SELECT. Please read my answer in the provided link once again.

P.S. I can't write your script now, I am answering using my mobile phone.

0 Kudos

Thank you for the quick response, let me look for other options if TMVL usage in select statement is ruled out

former_member186338
Active Contributor
0 Kudos

Other option is to use TIMEID property and combination of >= and < to get next year. Look on my code.

former_member186338
Active Contributor
0 Kudos

It's not possible to use "+1" in SELECT - only string concatenation is allowed.

You can play with TIMEID property to find the next year. I have answered this question number of times, please search my answers.

P.S. here https://archive.sap.com/discussions/thread/3944392

I use this approach to find the previous year. Same can be done for next year.