How to convert data in Periodic model with a YTD rate?

[alexandresamuel]

Hi experts,

I have the following questions:

My P&L model is a Peridic model which has no Substables (Flow) dimension so I can't use Business Rules.

In my model I have 3 currencies: EUR, CHF, BRL.

I need to convert datas with a YTD rate.

For example (datas are Periodic)

Account;Entity;Currency;Period;Amount

701000;Brazil;BRL;JAN;100

701000;Brazil;BRL;FEB;50

My rate is 3,243668 for JAN

My rate is 3,25056 for FEB

These rates are Average Cumulated Rate from the 1/1/2014.

So I need to realize following calculs:

For JAN: 100/3,243668 = 30,83

For FEB: 150/3,25056 = 46,15

So I have to read results like that in Periodic measure:

For JAN:

30,83

For FEB:

15,32 (46,15 - 30,83)

To convert datas, I need to use a script which allows to convert FEB datas in YTD, calculate difference between JAN and FEB and write result on FEB.

I use following script:

*LOOKUP TAUX

*DIM FXRATENTITY="GLOBAL"

*DIM FXRATE=COMPTE.RATETYPE

*DIM EUR:INPUTCURRENCY=CENTRE_COUT.CURRENCY

*DIM MEASURES="PERIODIC"

*ENDLOOKUP

*FOR %PERIODE%=%TEMPS_SET%

*WHEN COMPTE.RATETYPE

*IS AVG,CLO,OPE

*REC(EXPRESSION=((([MEASURES].[YTD])*1/lookup(EUR))-([MEASURES].[YTD],TEMPS.TMVL(-1,%PERIODE%),[DEVISE].[EUR])),DEVISE="EUR",TEMPS.TMVL(0,%PERIODE%))

*ELSE

*REC(DEVISE="EUR")

*ENDWHEN

*COMMIT

*NEXT

But when I validate my script, I have the following error:

Unknown dimension name in keyword: "TEMPS.*: (Cf Attachment).

Have you an idea for the reason of this message?

TMVL is it a good syntax for BPC 10.1 NW?

Have you another solution to realize my calculation?

Thanks for your help.

Alexandre

[former_member186338 · Accepted solution]

Hi Alexandre,

Can you explain the business logic of your calculations?

Also:

What will be the result for MAR:

701000;Brazil;BRL;MAR;80

My rate is 3,26000 for MAR

Then:

Why not to use normal currency conversion mechanism?

Vadim

[alexandresamuel · Accepted solution]

Hi Vadim,

To explain my calculation, in my example I have chosen 2014.02 period in my prompt when I execute package:

For the part of the script:

*REC(EXPRESSION=((([MEASURES].[YTD])*1/lookup(EUR))-([MEASURES].[YTD],TEMPS.TMVL(-1,%PERIODE%),[DEVISE].[EUR])),DEVISE="EUR",TEMPS.TMVL(0,%PERIODE%))

When I cut this line, I have:

1)

((([MEASURES].[YTD])*1/lookup(EUR))

I select my amount of FEB in YTD and / my amount by average cumulated rate of FEB:

So: 150/3,25056 = 46,15

2)

-([MEASURES].[YTD],TEMPS.TMVL(-1,%PERIODE%),[DEVISE].[EUR]))

I take the amount in YTD of the previous month (TMVL -1) which is already converted in EUR:

So: 100/3,243668 = 30,83

And I subtract 1) and 2) => 46,15 - 30,83 = 15,32

3)

DEVISE="EUR",TEMPS.TMVL(0,%PERIODE%))

I want to write 15,32 amount on EUR currency for my selected month.

So: 15,32 is written in Periodic measure => 46,15 in YTD measure for FEB.

If I take the same example for MAR, I will have:

701000;Brazil;BRL;MAR;80

My rate is 3,26000 for MAR

Calculation:

230 / 3,26000 = 70,55

70,55 - 46,15 = 24,40

So for MAR, I have:

24,40 for Periodic

70, 55 for YTD

I can't use normal currency conversion because there is a difference between amount converted in YTD and amount converted in Periodic:

If I realize conversion in Periodic:

JAN:

100 / 3,243668 = 30,83

FEB:

50 / 3,25056 = 15,38

MAR:

80 / 3,2600 = 24,54

Amount in YTD = 70,75 (30,83 + 15,38 + 24,54)

So I have a difference between the two methods:

70,75 - 70,55 = 0,2

I hope my example is clear enough.

Thanks for your help.

Alexandre

[former_member186338 · Accepted solution]

Sorry, this is not a business logic!

Vadim

[former_member186338 · Accepted solution]

And what do you mean by this strange syntax:

TEMPS.TMVL(-1,%PERIODE%)

May be:

[TEMPS].[TMVL(-1,%PERIODE%)] ?

And for target:

TEMPS.TMVL(0,%PERIODE%) ??

TEMPS=%PERIODE% ?

And scope is not defined for TEMPS...

Vadim

[former_member186338 · Accepted solution]

And, what is the name of account 701000?

Vadim

[alexandresamuel · Accepted solution]

Vadim,

I'm not sure to understand what you mean about business logic, if you mean business rules there is no business rule because because I don't have Flow dimension type in my model. If you want to know the process chain I use /CPMB/FX_RESTATMENT.

Can you explain me you need for this point?

About script, I have realized following modifications like you wrote:

*XDIM_MEMBERSET TEMPS=%TEMPS_SET%

*WHEN COMPTE.RATETYPE

*IS AVG

*REC(EXPRESSION=((([MEASURES].[YTD])*1/lookup(EUR))-([MEASURES].[YTD],[TEMPS].[TMVL(-1,2014.02)],[DEVISE].[EUR])),DEVISE="EUR",TEMPS=%TEMPS_SET%,MEASURES="PERIODIC")

*ELSE

*REC(DEVISE="EUR")

*ENDWHEN

*COMMIT

I can't use [TEMPS].[TMVL(-1,%TEMPS_SET%)], so I put [TEMPS].[TMVL(-1,2014.02)] for tests (I have an error message when I use %TEMPS_SET%).

Actually with my script, I have the following message:

Unknown dimension name in Keywords "Measures: when I had in target MEASURES="PERIODIC".

701000 is a sales Account.

Thanks for your help

Alexandre

[former_member186338 · Accepted solution]

Ups, if 701000 is a sales account then it's strange to convert sales to different currency using YTD amounts... And it's what "what you mean about business logic" - I want to understand the business value of the calculations you want to perform!

About Measures:

Never use PERIODIC as target in REC, REC always use default cube measure (PERIODIC in this case).

And before WHEN... add:

*XDIM_MEMBERSET MEASURES=PERIODIC

Vadim

P.S.  Please read:

Use UJKT for testing to avoid "I have an error message when I use %TEMPS_SET%"

P.P.S And never use useless COMMIT!

[alexandresamuel · Accepted solution]

Hi Vadim,

Thanks for your help.

According to you, it's a different way to convert datas but it's the way of my project. It seems to be a normative way in my environment.

My script seems to be better but I have always a problem to identify [TMVL(-1,%TEMPS_SET%)]

My script is actually like that:

*XDIM_MEMBERSET TEMPS=%TEMPS_SET%

*XDIM_MEMBERSET MEASURES=PERIODIC

*WHEN COMPTE.RATETYPE

*IS AVG

*REC(EXPRESSION=((([TEMPS].[%TEMPS_SET%],[MEASURES].[YTD],[DEVISE].[LC])*1/lookup(EUR))-([MEASURES].[YTD],[TEMPS].[TMVL(-1,%TEMPS_SET%)],[DEVISE].[EUR])),DEVISE="EUR",TEMPS=%TEMPS_SET%)

*ELSE

*REC(DEVISE="EUR")

*ENDWHEN

*COMMIT

I have realized UJKT Test and results are:

Validate:

UJK_VALIDATION_EXCEPTION:Unknown Dimension Name in Keyword: "[TEMPS].[ * ]:

Execute:

UJK_VALIDATION_EXCEPTION:Dimension set:"TEMPS" not assigned in Data Manager

When I replace [TEMPS].[TMVL(-1,%TEMPS_SET%)] by [TEMPS].[TMVL(-1,2014.02)] it's Ok.

It looks like TMVL syntax was no identify in my BPC NW 10.1.

Is it a syntactic problem?

Thanks

[former_member186338 · Accepted solution]

Hi Alexandre,

Have you read the document

referenced in

To test the script you have to provide members in data region to have non empty %TEMPS_SET%!

Please always provide screenshots to see the possible issues!

And never use useless COMMIT!

Vadim