Currency conv for multiple fiscal years only translates differences within the same fiscal year

[els_vanhoof]

We have the following currency translation script in our default logic:

*FOR %TIME% = %TIME_SET%

*RUN_PROGRAM CURR_CONVERSION

CATEGORY = %VERSION_SET%

CURRENCY=EUR, RUB

TID_RA = %TIME%

RATEENTITY = Global

OTHER = [ENTITY=%ENTITY_SET%]

*COMMIT

*NEXT

The logic works for our MTP version (inputs for DEC of multiple years). Input sheet contains e.g. 2017.DEC, 2018.DEC, 2019.DEC, 2020.DEC and 2021.DEC.

But not for a budget version in which we send data for multiple months in the same year: it works for January, but for the other months only the difference between this month and the previous month is converted: see screenshot.

currency-conversion.png

How can I create a script that correctly translates the currencies in both cases:

for multiple years for MTP version, for multiple months for all other version.

thank you!

[former_member186338 · Accepted solution]

You can use this code to distinguish between multiple years and single year:

*SELECT(%YS%,[YEAR],TIME,ID=%TIME_SET%) //Get list of years
*SELECT(%M%,[ID],TIME,YEAR=%YS% AND PERIOD=JAN) //Get list of JAN of years
*SELECT(%TIDS%,[TIMEID],TIME,ID=%M%) //Get list of TIMEID's
*SELECT(%GEIDS%,[ID],TIME,TIMEID>=%TIDS%) //GE ID's
*SELECT(%LEIDS%,[ID],TIME,TIMEID<=%TIDS%) //LE ID's
*SELECT(%GETIDS%,[TIMEID],TIME,ID=%GEIDS%) //GE TIMEID's
*SELECT(%LETIDS%,[TIMEID],TIME,ID=%LEIDS%) //LE TIMEID's
*SELECT(%T1%,[ID],TIME,ID=%M% AND TIMEID=%LETIDS%) // First Time ID
*SELECT(%T2%,[ID],TIME,ID=%M% AND TIMEID=%GETIDS%) //Last Time ID
*SELECT(%TID2%,[TIMEID],TIME,ID=%T2%) //TIMEID of the last Time ID
*SELECT(%MULTIYEAR%,[ID],TIME,ID=%T1% AND TIMEID<%TID2%) //Value if multiple years or nothing
*SELECT(%SINGLEYEAR%,[ID],TIME,ID=%T1% AND TIMEID=%TID2%) //Value if single year or nothing

*FOR %TEST1%=%MULTIYEAR% //If multiple years
*FOR %TIME% = %TIME_SET%
*RUN_PROGRAM CURR_CONVERSION
CATEGORY = %VERSION_SET%
CURRENCY=EUR, RUB
TID_RA = %TIME%
RATEENTITY = Global
OTHER = [ENTITY=%ENTITY_SET%]
*ENDRUN_PROGRAM
*NEXT
*NEXT

*FOR %TEST2%=%SINGLEYEAR% //If single year
*RUN_PROGRAM CURR_CONVERSION
CATEGORY = %VERSION_SET%
CURRENCY=EUR, RUB
TID_RA = %TIME_SET%
RATEENTITY = Global
OTHER = [ENTITY=%ENTITY_SET%]
*ENDRUN_PROGRAM
*NEXT

A bit complex, but you can test it in UJKT 🙂

[els_vanhoof · Accepted solution]

It works perfect! thanks a lot!

[els_vanhoof]

Dear Vadim

In the business rules, there are only two relevant rules: AVG and KEYFIGURE.

Here is the set-up for the AVG one.

If I delete the *FOR %TIME% = %TIME_SET% statement in the logic script, all versions, except MTP are working fine. I found this solution in this question: https://archive.sap.com/discussions/thread/1709018 also in note

For MTP the amounts are not converted.

kind regards

[JohnL]

Hi Els,

Why do you have the periodic calc option set?

Also, do you have the correct exchange rates?

Are you using Standard model or Embedded?

YTD storage or Periodic Storage?

Thanks,

[els_vanhoof]

Hi

AVG for P&L (only P&L in our model)

we have rates loaded for the versions and time periods.

standard model

YTD storage.

regards

[former_member186338]

For/Next solution is required for multiple years... not clear in your case!

Please explain in details MTP conversion logic!

One month - DEC is selected or what?

[els_vanhoof]

For MTP we create a new version each year so MTP2016, we ask for input for 2017.DEC, 2018.DEC, 2019.DEC, 2020.DEC and 2021.DEC.

For other budget version we ask for input for all months in one year so e.g. budget for 2018.JAN -> 2018.DEC.

Now, after data has been send, the default logic kicks in and we require to do currency conversion. We do this with the script above, which works fine for MTP, but not for budget!

If we omit the For clause, it works for budget, but not for MTP. So we are looking for a way to use currency conversion in default logic that works for all versions.

[former_member186338]

Please describe your input form! May be some screenshots...

Do you save data for MTP in the same report as for budget?

[els_vanhoof]

users open the input form for version MTP2016 - their entity

They input data for account - intraco time combinations: e.g. account DIST - I_NONE for periods

2017.DEC, 2018.DEC, 2019.DEC, 2020.DEC and 2021.DEC. Account has AVG as type.

see screenshot

close the input form

conversion works fine

open input form for PIB - their entity

They input data for account - intraco time combinations: e.g. account DIST - I_NONE for periods 2017.JAN, 2017.FEB,... 2017.DEC. Account has AVG as type.

see screenshot

conversion acts strange with script from above

[former_member186338]

Sorry, but why do you decided to use "Insert File" instead of "Insert Image"??? VERY hard to read!

[former_member186338]

You don't need for/next in general!

Please show the rule setup...