Usage of TMVL in scoping

[Former Member]

Hi team,

We need to scope last 15 months for Time dimension and copy the data to new version.

The problem is we are unable to scope 15 months data. We dont have any specific property which identifies the last 15 months.

User doesnt want to change the Dimension.

We then tried using TMVL function as below. 

2014.01- base member of TIME dimension

*SELECT(%LAST_PER%,"ID",TIME,"ID=TMVL(-15,2014.01)

*SELECT(%HIST_PERS%,"[ID]",TIME,"[ID]>= %LAST_PER% AND [ID]<= 2014.01")

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET TIME_PERIOD = %HIST_PER%

*WHEN VERSION

*IS "ACTUAL"

*REC(EXPRESSION = %VALUE%, VERSION="FY15_P08")

*ENDWHEN

LGX OUTPUT

#dim_memberset=1

VERSION:ACTUAL,1 in total.

REC :%VALUE%

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 107  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :0.00 ms.

107  RECORDS ARE GENERATED.

CALCULATION END.

TIME scoping is not considered at all, Only Version scoping is taken.

Is there a way to scope TIME dimension with old periods.

We are on BPC 10.1 Classic model on HANA data base.

Thanks in advance

[former_member186338 · Accepted solution]

Easy:

Let's assume that you have selected the current month in %TIME_PERIOD_SET%=2014.01

Then:

*XDIM_MEMBERSET TIME_PERIOD = TMVL(-15,%TIME_PERIOD_SET%),TMVL(-14,%TIME_PERIOD_SET%),TMVL(-13,%TIME_PERIOD_SET%)...

You can't use TMVL in SELECT, SELECT is calculated before TMVL

Vadim

[Former Member · Accepted solution]

HI Vadim,

Thanks for your response.

As suggested removed TMVL in SELECT statements. Modified the script as below -

*SELECT(%CURR_PER%,"[START_PER]",VERSION,"[ID]='ACTUAL'")   --- gets current period

*XDIM_MEMBERSET VERSION = ACTUAL

*XDIM_MEMBERSET TIME_PERIOD = TMVL(-1,%CURR_PER%)

*WHEN VERSION

*IS "ACTUAL"

*REC(EXPRESSION = %VALUE%, VERSION="FY15_P08")

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

UJK_VALIDATION_EXCEPTION:Invalid TMVL return value

i even tried removing select statement and setting CURR_PER = 2014.01 in DATA REGION in UJKT.

The result is the same. 2014.01 is the base member in TIME_PERIOD dimension.

Only if i hard code the TIME value in scoping teh logic is getting executed

So is it not possible to have variable used under TMVL function during scoping?

Can you please assist on this!

[former_member186338 · Accepted solution]

You can't set "CURR_PER = 2014.01 in DATA REGION"!

Check your TIME_PERIOD dimension for TIMEID property - has to be correctly maintained for all base members for TMVL to work correctly.

Provide screenshots of UJKT...

For the code:

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

*XDIM_MEMBERSET TIME_PERIOD = %CURR_PER%

*XDIM_MEMBERSET TIME_PERIOD = TMVL(-1,%CURR_PER%)

Vadim

[former_member186338 · Accepted solution]

P.S. Provide screenshots of administration screen for VERSION and TIME_PERIOD dimensions

[former_member186338 · Accepted solution]

Just tested in the shell copy:

Script:

*SELECT(%CURR_PER%,"[START_PER]",CATEGORY,"[ID]='Actual'") 

*XDIM_MEMBERSET TIME = %CURR_PER%

*XDIM_MEMBERSET TIME = TMVL(-1,%CURR_PER%),TMVL(-2,%CURR_PER%)

UJKT result for EXECUTE (Simulate):

LGX:

*XDIM_MEMBERSET TIME = 2014.01

*XDIM_MEMBERSET TIME = 2013.12 , 2013.11

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

FILE:\ROOT\WEBFOLDERS\ENV1 \ADMINAPP\Planning\TEST.LGF

USER:V.KALININ

APPSET:ENV1

APPLICATION:Planning

SCRIPT RUNNING TIME IN TOTAL:0.00 s.

Vadim

[Former Member]

Hi Vadim,

Apologies for the confusion.

START_PER property under VERSION dimension is saved as 2014,01 instead of 2014.01. Hence the error in UJKT.

I have changed the property and the logic is working fine now.

Thanks a lot for your response!

Thanks . Raghu