Currency conversion issue in periodic model using YTD value

[Former Member]

Hi Experts,

I have a periodic model and due to business requirement, they wanted currency translation to be performed by using YTD value. Illustration is as below. Below example shows the currency conversion from LC -> USD.

Users will maintain the value in LC in periodic model. Then, the currency conversion value for each period will be calculated based on YTD value. Taking month 2 as an example, the LC value is 200. The YTD value is 300. The YTD translated value will be 1255.230126 (Formula: 300/0.239). The currency translated value that should be written back to periodic model is 839.7752024 (Formula: 1255.230126 - 415.4549231). It is current month minus previous month value in YTD.

We have provided logic scripts to perform this and the result in green above will be written in to the periodic model for currency translation. The issue happen when the user view the report in YTD measures for USD currency.

Notice that there is a blank value in LC for month 4. The total YTD value in USD after currency translation should be 30667.22. Right now, the total YTD amount written to periodic model up to month Dec is 30690.98. This is because the difference in the translation rate in month 4. If we also write the translated value difference for month 4 back to the model, then the value will be correct.

User now asked us to write the value difference for USD even though there is no LC value for month 4. Does it make sense or there is any better way for this?

The current logic script that we have is as below. In order to handle the situation above, I have tested adding *WHEN_REF_DATA = MASTER_DATA in the script. Additional line added in in bold below. I understand that it might cause performance issue but I couldn't find a better way for this as we could not scope the data as the LC value is empty. Appreciate your suggestion / help on this. Let me know if more clarification is needed.

*XDIM_MEMBERSET CATEGORY = Actual
*XDIM_MEMBERSET ACCOUNT = BAS(PL00000000)
*XDIM_MEMBERSET AUDITTRAIL = MFR_Input
*XDIM_MEMBERSET TIME = BAS(%TIME_SET%)
*XDIM_MEMBERSET ENTITY = BAS(%ENTITY_SET%)
*XDIM_MEMBERSET FLOW = BAS(F_Total),F999
*XDIM_MEMBERSET INTERCO = <ALL>
*XDIM_MEMBERSET RPTCURRENCY = LC
*XDIM_MEMBERSET MEASURES = PERIODIC

*WHEN_REF_DATA = MASTER_DATA

*LOOKUP Rates
*DIM PT: TIME = TIME.PREVMONTH
*DIM PT: R_ACCOUNT = AVG
*DIM PT: R_ENTITY = Global
*DIM PT: CATEGORY = CATEGORY
*DIM PT: INPUTCURRENCY = ENTITY.CURRENCY
*DIM PT: MEASURES = PERIODIC
*FOR %S_TIME% = %TIME_SET%
*DIM C_%S_TIME%: TIME = %S_TIME%
*DIM C_%S_TIME%: R_ACCOUNT = AVG
*DIM C_%S_TIME%: R_ENTITY = Global
*DIM C_%S_TIME%: CATEGORY = CATEGORY
*DIM C_%S_TIME%: INPUTCURRENCY = ENTITY.CURRENCY
*DIM C_%S_TIME%: MEASURES = PERIODIC
*NEXT
*ENDLOOKUP

*FOR %CTIME% = %TIME_SET%
*XDIM_MEMBERSET TIME = %CTIME%
*WHEN ENTITY.CURRENCY
*IS USD
*REC(FACTOR = 1, RPTCURRENCY = USD)
*ELSE
*WHEN TIME.PERIOD
*IS JAN
*REC(EXPRESSION = ([MEASURES].[YTD]/LOOKUP(C_%CTIME%)), RPTCURRENCY = MYR)
*ELSE
*REC(EXPRESSION = ([MEASURES].[YTD]/LOOKUP(C_%CTIME%)) - (([MEASURES].[YTD],[TIME].[TMVL(-1,%CTIME%)])/LOOKUP(PT)), RPTCURRENCY = USD)
*ENDWHEN
*ENDWHEN
*NEXT

[former_member186338]

Please remove not related tag:

SAP Business Planning and Consolidation, version for the Microsoft platform

[former_member186338 · Accepted solution]

"*REC(EXPRESSION = %VALUE% - ([TIME].[TMVL(-1,%CTIME%)]), AUDITTRAIL = YTD_TEST)" - wrong idea!

Please read my blog: https://blogs.sap.com/2018/11/02/periodic-ytd-conversion-using-script-logic-in-bpc-nw/

Perform:

1. Periodic->YTD to special audittrail

2. Perform FX conversion of this special audittrail (using standard currency conversion routine)

3. Perform YTD-> periodic conversion of this special audittrail in converted currency

[Former Member · Accepted solution]

Hi Vadim,

Thanks for sharing the blog. It works well now. I have marked "Accept" on what you have suggested. Thanks for your help.

[former_member186338]

P.S.:

By the way, I have posted number of blogs about script logic:

https://blogs.sap.com/2018/11/14/how-to-copy-data-between-models-in-bpc-script-logic/

https://blogs.sap.com/2018/11/04/how-to-check-that-only-single-member-is-passed-from-dm-prompt-in-bp.../

https://blogs.sap.com/2018/11/04/how-to-calculate-next-or-previous-time-member-in-bpc-script-logic/

https://blogs.sap.com/2018/11/14/how-to-perform-calculations-in-bpc-script-logic/

Can be useful for you!

[Former Member]

Hi Vadim,

Thanks for sharing. I will read the blog. Thank you!

[former_member186338]

LOOKUP:

1. Common dimension declarations are provided without label

2. FOR/NEXT is not necessary in this case

*LOOKUP Rates
*DIM R_ACCOUNT = AVG
*DIM R_ENTITY = Global
*DIM CATEGORY = CATEGORY
*DIM INPUTCURRENCY = ENTITY.CURRENCY
*DIM MEASURES = PERIODIC
*DIM PT: TIME = TIME.PREVMONTH
*DIM CT: TIME = TIME
*ENDLOOKUP

P.S. If you received a correct answer - please accept it!

[Former Member]

Hi Vadim,

Thanks for the advise. I have one more clarification. I am still doing some testing and I will mark this question complete after my testing.I have performed PERIODIC to YTD conversion using some dedicated AUDITTRAIL member. Then, I have performed the currency conversion. Amount converted are in YTD amount. I would like to have the result in green colour below. It will be current month minus previous month value. How should I do it in script using Expression and write it back to the model? I tried the following but the result is incorrect. Appreciate your advise on this.

*REC(EXPRESSION = %VALUE% - ([TIME].[TMVL(-1,%CTIME%)]), AUDITTRAIL = YTD_TEST)

[former_member186338]

In general the solution is:

First perform PERIODIC to YTD conversion using some dedicated AUDITTRAIL member, or create a YTD model and convert to this model.

Then do currency conversion.

[Former Member]

Hi Vadim,

Thanks for the advice. Really appreciate it. Would you mind to share on what is wrong on the LOOKUP syntax? I have validated the scripts and it doesn't show any error.

Thank you.

[former_member186338]

Let's forget for a moment currency conversion and concentrate on PERIODIC to YTD conversion:

We have some data in account PL110 in some months of Year 2007 in PERIODIC model PLANNING.

And we want to convert this data to YTD values in account PL120 using script:

*XDIM_MEMBERSET AUDITTRAIL=Input
*XDIM_MEMBERSET ENTITY=DE
*XDIM_MEMBERSET ACCOUNT=PL110
*XDIM_MEMBERSET CATEGORY=Budget
*XDIM_MEMBERSET TIME=BAS(2007.TOTAL)
*XDIM_MEMBERSET PRODUCT=ProductA
*XDIM_MEMBERSET INTERCO=ThirdParty
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET MEASURES=PERIODIC

*WHEN ACCOUNT
*IS *
*REC(EXPRESSION=[MEASURES].[YTD],ACCOUNT=PL120)
*ENDWHEN

The result is here:

The values for 2007.05 and 2007.07...2007.12 are missing - incorrect compared to YTD MEASURE

If we use the correct conversion script:

*XDIM_MEMBERSET AUDITTRAIL=Input
*XDIM_MEMBERSET ENTITY=DE
*XDIM_MEMBERSET ACCOUNT=PL110
*XDIM_MEMBERSET CATEGORY=Budget
*XDIM_MEMBERSET TIME=BAS(2007.TOTAL)
*XDIM_MEMBERSET PRODUCT=ProductA
*XDIM_MEMBERSET INTERCO=ThirdParty
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET MEASURES=PERIODIC
*SELECT(%Y%,[YEAR],TIME,[ID]=2007.TOTAL)

*WHEN TIME.MONTHNUM
*IS 1
*REC(EXPRESSION=%VALUE%,TIME=%Y%.01,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.02,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.03,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.04,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.05,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.06,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.07,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.08,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.10,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*IS 2
*REC(EXPRESSION=%VALUE%,TIME=%Y%.02,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.03,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.04,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.05,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.06,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.07,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.08,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.10,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*IS 3
*REC(EXPRESSION=%VALUE%,TIME=%Y%.03,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.04,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.05,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.06,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.07,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.08,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.10,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*IS 4
*REC(EXPRESSION=%VALUE%,TIME=%Y%.04,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.05,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.06,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.07,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.08,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.10,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*IS 5
*REC(EXPRESSION=%VALUE%,TIME=%Y%.05,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.06,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.07,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.08,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.10,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*IS 6
*REC(EXPRESSION=%VALUE%,TIME=%Y%.06,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.07,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.08,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.10,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*IS 7
*REC(EXPRESSION=%VALUE%,TIME=%Y%.07,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.08,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.10,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*IS 8
*REC(EXPRESSION=%VALUE%,TIME=%Y%.08,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.10,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*IS 9
*REC(EXPRESSION=%VALUE%,TIME=%Y%.09,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.10,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*IS 10
*REC(EXPRESSION=%VALUE%,TIME=%Y%.10,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*IS 11
*REC(EXPRESSION=%VALUE%,TIME=%Y%.11,ACCOUNT=PL120)
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*IS 12
*REC(EXPRESSION=%VALUE%,TIME=%Y%.12,ACCOUNT=PL120)
*ENDWHEN

The result will be correct:

[former_member186338]

P.S. Also your LOOKUP syntax is incorrect!