- SAP Community
- Products and Technology
- Financial Management
- Financial Management Q&A
- Currency conversion issue in periodic model using ...

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

Currency conversion issue in periodic model using YTD value

former_member587479

Explorer

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

on 10-31-2018 9:34 AM

680

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

- SAP Managed Tags:
- SAP Business Planning and Consolidation, version for SAP NetWeaver

former_member186338

Active Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

11-08-2018
1:53 PM

"*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_member587479

Explorer

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

11-15-2018
8:09 AM

former_member186338

Active Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

11-15-2018
8:25 AM

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-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_member587479

Explorer

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

11-15-2018
8:51 AM

former_member186338

Active Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

11-01-2018
7:36 AM

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_member587479

Explorer

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

11-08-2018
1:31 PM

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

Active Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

10-31-2018
10:55 AM

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_member587479

Explorer

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

11-01-2018
7:18 AM

former_member186338

Active Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

10-31-2018
10:40 AM

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

Active Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

10-31-2018
10:44 AM

Ask a Question

Related Content

- Exchange Rate Conversion for Company Code Currency in Financial Management Q&A
- SAP advanced treasury and risk management package for GROW with SAP in Financial Management Blogs by SAP
- Advanced treasury and risk management package for RISE with SAP in Financial Management Blogs by SAP
- Unveiling the new functionality in 2024 of SAP PAPM Cloud: Welcome to Universal Model! in Financial Management Blogs by SAP
- Intrastat 101 - SAP S/4HANA for international trade and SAP GTS, edition for SAP HANA in Financial Management Blogs by SAP

Top Q&A Solution Author

User | Count |
---|---|

9 | |

5 | |

2 | |

1 | |

1 | |

1 | |

1 | |

1 | |

1 | |

1 |

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.