5 Replies Latest reply: Aug 11, 2017 7:55 AM by susant Kumar swain RSS

    Multiple currency conversion at selected date

    Rakesh Pareek

      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).

        • Re: Multiple currency conversion at selected date
          Varun Prakash Paulraj

          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

          • Re: Multiple currency conversion at selected date
            Varun Prakash Paulraj

            Check if the attached helps.

             

            Let me know if my understanding is wrong.

              • Re: Multiple currency conversion at selected date
                Rakesh Pareek

                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).


                 

                  • Re: Multiple currency conversion at selected date
                    susant Kumar swain

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

                     

                     

                • Re: Multiple currency conversion at selected date
                  Andrew Walker

                  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