TMVL

[Former Member]

Hello Experts,

I have this script which i am running .I am getting an error:

RUN_LOGIC:Member "'2013.09'" not exist

The logic is :

*XDIM_MEMBERSET TIME = TMVL(-12,'%TIME_SET%'),TMVL(-12,'%YEAR%.12'),%TIME_SET%

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET ACCOUNT = BAS(REVENUE)

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET AUDITTRAIL = BAS(AuditTotal)

*WHEN TIME

*IS TMVL(-12,'%YEAR%.12'),%TIME_SET%

*WHEN ACCOUNT

*IS BAS(REVENUE)

*REC(EXPRESSION = %VALUE%-[TIME].[TMVL(-12,'%TIME_SET%')],ACCOUNT = S_Rev, TIME = TMVL(1,'%TIME_SET%'))

*ENDWHEN

*ENDWHEN

Your help will be appreaciated.I think its the syntax on TMVL

Regards

Lawrence

[Former Member · Accepted solution]

Hi Vadim,

Thanks,i never thought of using a custom measure.

I have attached a table of data.You will see the formula in the Rolling revenue.

Thats what i am trying to achieve

Regards

Lawrence

[Former Member · Accepted solution]

They must select current year and month

eg 2015.10

[former_member186338 · Accepted solution]

Month for what?

[former_member186338 · Accepted solution]

Month to use as current month or month to store result or????

[Former Member · Accepted solution]

This is the month which will determine the rolling forecast

[Former Member · Accepted solution]

to store result

[former_member186338 · Accepted solution]

OK, let me describe the requirements. Please correct if I am wrong...

User is launching DM package and has to select some single month: 2015.02 (for example)

You want to perform calculations on the ACCOUNT = BAS(REVENUE) with the following logic (based on the attached file😞

Store the result in ACCOUNT = S_Rev

2015.02 (target) = 2015.02 + 2014.12 - 2014.02

(The logic in your test script is different)

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Yes thats the business requirement.The scrpt is not doing that,but thats what i want to achieve

Regards

Lawrence

[former_member186338 · Accepted solution]

The correct script will be:

//%TIME_SET% = 2015.02

*SELECT(%Y%,[YEAR],TIME,ID=%TIME_SET%)

*XDIM_MEMBERSET TIME=TMVL(-12,%TIME_SET%),TMVL(-12,%Y%.12),%TIME_SET%

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET ACCOUNT = BAS(REVENUE)

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET AUDITTRAIL = BAS(AuditTotal)

*WHEN TIME

*IS TMVL(-12,%TIME_SET%) //2014.02

*REC(EXPRESSION = -%VALUE%,ACCOUNT=S_Rev,TIME=%TIME_SET%) //negative!

*ELSE //2015.02,2014.12

*REC(EXPRESSION = %VALUE%,ACCOUNT=S_Rev,TIME=%TIME_SET%)

*ENDWHEN

Vadim

P.S. Never use %YEAR% variable - it's based on the server time and contain current year!

[Former Member · Accepted solution]

Thanks Vadim,

However,if i run the package i get an error:

RUN_LOGIC:Member "'2013.09'" not exist

model: Finance. Package status: ERROR

Regards

Lawrence

[former_member186338 · Accepted solution]

Screenshot of the UJKT please! And screenshot of TIME dimension administration - to ensure you have 2013.09 and correctly maintained TIMEID property. In my system this script is working fine!

Vadim

P.S. And read again

Version/SP???

[Former Member · Accepted solution]

Hi Vadim,

Thanks so much for your step by step help

[former_member186338]

Hi Lawrence,

Use TMVL parameters without quotes...

TMVL(-12,%TIME_SET%)...

But in general your script is absolutely strange and incorrect Can you explain the business requirements? Please, read

Vadim

[Former Member]

Thanks Vadim,

Below is the detail of what i want to achieve:

The Business Requirement is to calculate a 12 month Rolling Revenue in a YTD Application.

Rolling revenue =Current Month + December -the beggining of the period because its included in the ytd figure.

EG

2014.09-----2014.12------2015.08

--- ---------12 months------------   

Dimensions Included:

TIME

ENTITY 

RPTCURRENCY

ACCOUNT

CATEGORY

AUDITTRAIL

This is going to be executed via data manager and the selections are:

category

entity

Time

Current Logic:

// CALCULATE ROLLING REVENUE, MATERIAL COST MONTHLY

//*SELECT(%MY_YR%,YEAR,CATEGORY,ID = Actual)

//*SELECT(%MY_MTH%,"[MONTH]","CATEGORY","[ID] = 'Actual'")

*XDIM_MEMBERSET TIME = TMVL(-12,%TIME_SET%),TMVL(-12,%YEAR%.12),%TIME_SET%

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET ACCOUNT = BAS(REVENUE)

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

*XDIM_MEMBERSET AUDITTRAIL = BAS(AuditTotal)

*WHEN TIME

*IS TMVL(-12,%YEAR%.12),%TIME_SET%

*WHEN ACCOUNT

*IS BAS(REVENUE)

*REC(EXPRESSION = %VALUE%-[TIME].[TMVL(-12,%TIME_SET%)],ACCOUNT = S_Rev, TIME = TMVL(1,%TIME_SET%))

*ENDWHEN

*ENDWHEN

Your help will be greatly appreciated

[former_member186338]

Sorry, but "Rolling revenue =Current Month + December -the beggining of the period" - not clear!

Can you provide a small table with sample data?

By the way, have you thought about custom measure creation for the same?

Vadim

[former_member186338]

And please explain what do you want the user to select in %TIME_SET%?