Multiple currency conversion at selected date

[rkspareek1992]

Hi,

As per user requirement, in data we have many currencies and wants  to change those currencies  into different currencies at selected date.

I have attached excel file of my data format and also  attached the format in which I have currency conversion format(as in Table).

Kindly suggest how can I change it (at front end).

[techvarun]

You just need to load the file and create a table with

expression Rate* Amt

Dimension From Currency and ToCurrency and Date.

it will work by default.

Thanks,

Varun

[techvarun]

Check if the attached helps.

Let me know if my understanding is wrong.

[rkspareek1992]

Thanks for suggestion,

But I need rate as of  selected date. Means when I select 09-07-2017, then whole from currency to to currency values should be converted at the rate as of 09-07-2017. As per data there is no rate on 09-07-2017, so this should fetch rate as of last updated rate (as of 08-07-2017).

[effinty2112]

Hi Rakesh,

There are a few steps to take to resolve this.

We need to take your table of exchange rates and make one that defines the period (From and To dates) that the rates are valid for. Then we need to create a calendar that will give every date from first to last (today). The we need to associate each day in the calendar with the exchange rate periods, for this we use the IntervalMatch function. Run this script, don't worry about the synthetic table the IntervalMatch function creates this, it's expected and it not harmful.

Conversions:

Load

Amt,

[From Currency] & '|' &  [To Currency] as Currencies;

LOAD [From Currency],

     [To Currency],

     Amt

FROM

CurrencyConversion.xlsx

(ooxml, embedded labels, table is DataFormat);

[Exchange Rates]:

Load

[From Currency] & '|' &  [To Currency] as Currencies,

Date,

     Rate;

LOAD [From Currency],

     [To Currency],

     Date,

     Rate

FROM

CurrencyConversion.xlsx

(ooxml, embedded labels, table is RateTableFormat);

[Ordered Rates]:

LOAD

Currencies,

Rate,

Date as From,

If(Currencies = Previous(Currencies),Date(Previous(Date)-1), Today()) as To

Resident [Exchange Rates] Order by Currencies, Date desc;

Drop table [Exchange Rates];

FirstDate:

Load min(Temp) as FirstDate;

LOAD FieldValue('From',IterNo()) as Temp

AutoGenerate 1 While not IsNull(FieldValue('From',IterNo()));

Let vFirstDate = Floor(Peek('FirstDate',0,'FirstDate'));

DROP Table FirstDate;

LET vToday = Floor(Today());

[Calendar]:

LOAD

*,

Ceil((PeriodID+1)/3) as QPeriodID

;

LOAD

*,

AutoNumber(MPeriod, 'MPeriodID') as PeriodID

;

Load

Year(Temp_Date) * 100 + Month(Temp_Date) as MPeriod,

Year(Temp_Date) & 'Q' & Ceil(Month(Temp_Date) / 3) as QPeriod,

Year(Temp_Date) & 'W' & Right('0'&Week(Temp_Date),2) as WPeriod,

Date(Temp_Date-WeekDay(Temp_Date),'DD/MM/YYYY') as WeekBeg,

'Q' & Ceil(Month(Temp_Date) / 3) as Quarter,

Week(Temp_Date) as Week,

Div((Temp_Date-$(vFirstDate)),7)   as WeekID,

Month(Temp_Date) as Month,

Month(Temp_Date)  & ' ' & Year(Temp_Date) as MthYear,

Date(Temp_Date,'$(DateFormat)') as Date;

Load $(vFirstDate) -1 + IterNo() as Temp_Date

AutoGenerate 1 While $(vFirstDate) -1 + IterNo() <= $(vToday) ;

IntervalMatch:

IntervalMatch(Date) LOAD From, To Resident [Ordered Rates];

Now if you add dates to your table of currency conversions the data model will be able to associate the currencies and the date to the correct conversion date.

Regards

Andrew

[qliksus]

You can create a calendar table using the below script

Amount:
LOAD
[From Currency] &'-'& [To Currency] as Key,
[From Currency],
[To Currency],
Amt
FROM
CurrencyConversion.xlsx
(ooxml, embedded labels, table is DataFormat);

Rate:
LOAD
[From Currency] &'-'& [To Currency] as Key,
[From Currency] as Frm1,
[To Currency] as To1,
Date,
Rate
FROM
CurrencyConversion.xlsx
(ooxml, embedded labels, table is RateTableFormat);

Let vStartDate = '07/01/2017' ;
Let vEnddate = '07/31/2017';

calendar:
load

date(date#('$(vStartDate)')+ IterNo()-1) as Date,
date(date#('$(vStartDate)')+ IterNo()-1) as Date1

AutoGenerate(1)
While date('$(vStartDate)'+ IterNo()-1 ) <=date('$(vEnddate)') ;

In the front end use the below expression

Exp: pick( IsNull([From Currency])+2 , sum({<Date={'$(=max({1<Date={"<$(=max(Date))"}>}Date))'}>}Amt)*sum({<Date={'$(=max({1<Date={"<$(=max(Date))"}>}Date))'}>}Rate),

sum(Amt)*sum(Rate))