Currency Exchange Help!!!

[amit_saini]

Hi Folks,

Attached is my sample application.

From vertical list you can see , we are having some currencies and on text box there corresponding exchange rate.

Now what I'm looking here below in table is to show all values in USD , doesn't matter from which particular plant  values are coming ,

table should show you values in USD only!

Please help for the solution!

Thanks

AS

[amit_saini]

sunindia‌ Avinashelite‌swuehl‌jagan‌

Any suggestion???

Thanks
AS

[sunny_talwar]

Hey Amit,

What is the expected output?

[swuehl]

Your currency table shows multiple exchange rates per currency (rates on different dates).

So you would need to clarify how you want to calculate your dollar amounts.

Also, your fact table shows some fields that are names like 'Net Dollar Value of Inventory Adjustments'

Are these already in USD or in some other currency.

Best, post a detailed description of you data model and requirements

[amit_saini]

Hi,

As of now the data inside table is in Local currency and the requirement is to show all data in USD.

Just for example suppose "KCC" is having local currency as "CNY" and conversion from CNY to USD based on latest date is 8.28

so that output should be like attached , all numbers has to multiply by 8.28 , so that I can get figures in USD.

Background:

All the data in local currency I'm getting from Excel sheets and for exchange rate I'm using SAP TCURR table

TCURR:

LOAD //MANDT,

    KURST,

    FCURR,

    TCURR as [Local currency],

    (99999999-GDATU) as Latest_date,

    UKURS,

    FFACT,

    TFACT

FROM

D:\qvprod\qvd\SAP\KTX_100\TCURR.qvd

(qvd)

where KURST='M' and FCURR='USD' and match(TCURR,'BRL', 'CNY' ,'CZK' ,'EUR', 'GBP', 'INR','YEN');

This below is the expresion for getting latest exchange rate based on current date:

Only({<Latest_date={$(=Max(Latest_date))}>} (UKURS))

Thanks,

AS

[swuehl]

Any reason you need to keep all the historic exchange rates?

Or can you flag the latest date per currency in the script, i.e. the rate you want to use for your calculations?

Then your expression might look like

=sum({<LatestFlag = {1}>} [Value of material before counting] * UKURS)

[sunny_talwar]

In addition why not do the currency conversion in the script and create a field which is purely in USD?

[amit_saini]

Sorry not sure how to get that script , please help if you can!

Thanks,

AS

[swuehl]

For the flag, you can use something like

TCURR:

LOAD //MANDT,

    KURST,

    FCURR,

    TCURR as [Local currency],

    (99999999-GDATU) as Latest_date,

    UKURS,

    FFACT,

    TFACT

FROM

D:\qvprod\qvd\SAP\KTX_100\TCURR.qvd

(qvd)

where KURST='M' and FCURR='USD' and match(TCURR,'BRL', 'CNY' ,'CZK' ,'EUR', 'GBP', 'INR','YEN');

LEFT JOIN (TCURR)

LOAD

     [Local currency],

     Max([Latest_date]) as [Latest_date],

     1 as LatestFlag

RESIDENT TCURR

GROUP BY [Local currency];

[sunny_talwar]

Do not have the source data files, but the idea is like this:

Fact:

LOAD Price,

          Value,

          [Local Currency]

FROM Source;

Left Join (Fact)

LOAD Currency as [Local Currency],

          [Exchange Rate],

FROM Currency

Where "Use a where statement to restrict the currency exchange rates for the max date here"

FinalFact:

LOAD Price * [Exchange Rate] as [Price in USD],

          Value * [Exchange Rate] as [Value in USD],

          ...

Resident Fact;

DROP Table Fact;

You can also use ApplyMap() instead of Left Join which might give you a better performance