Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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
Check if the attached helps.
Let me know if my understanding is wrong.
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).
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
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))