2 Replies Latest reply: Mar 12, 2018 1:42 PM by Jahanzeb Hashmi RSS

    Exchange Rates

    Fernanda Gomez

      Hi,

       

      I need some help to calculate exchange rate. I have two tables with each of the programs and their corresponding amount and commit date in each currency. A program can have a different currency for each table.
      On the other hand I have a table with currency source, currency destination, date and exchange rate. I need to add to each of the tables the amount and the commit according to the currency filtered by the user (by default EURO). The program table must calculate the exchange rate with the date of each of the programs, while the pipeline to calculate the commit must be done with the date of the reporting date filtered by the user (by default today ()).
      I was thinking of duplicating the exchange rate table and join them to each of the tables but in my real app each program can have up to 10 different currency types but the model would be complicated and the execution time would increase.

      The tables should come out with these values without any filter type indicated (default today () and EUROS)


      PROGRAM:

        

      PROGRAMDATECURRENCY_PGAmountAmount €
      P12016-03-01PLN10024,534
      P22016-07-21USD285260,76246
      P32016-12-25EUR5656
      P42016-06-18GBP7482,8985
      P52017-03-15MXN34615,04347796
      P52017-08-24MXN45819,91304308
      P62017-04-10EUR1212
      P72018-10-31PLN432100,8403344
      P82018-11-04USD2319,00041316

       

      PIPELINE:

        

      PROGRAMDATE_PLCURRENCY_PLCommitCommit €
      P12016-10-08USD8973,5233379
      P22016-12-21PLN28566,5266095
      P32016-03-18EUR5656
      P32016-12-25GBP7887,3460247
      P42016-11-05USD126104,08922
      P42017-04-26MXN25810,8357833
      P52018-08-24PLN685159,89729
      P62017-10-31GBP1213,43785
      P72018-11-30EUR8080
      P72018-12-28EUR132132
      P82018-12-11USD6654,5229247

       

       

      I attach the qvf.


      Thank you very much in advance.

        • Re: Exchange Rates
          dion verbeke

          Have a look at the ExpandInterval from Qlik Components. It has helped me in these cases, although it can blow up the size of your document.

           

          Kind Regards,

           

          Dion

          • Re: Exchange Rates
            Jahanzeb Hashmi

            One long way to do that

            =if(CURRENCY_PG='USD',AMOUNT*Only({<C_CURRENCY_A={'USD'},C_CURRENCY_B={'EUR'},CALENDARDATE={'2016-03-01'}>}N_FX_VALUE),0)

             

            I added just for USA

            You can use today for the date my formate is different so I used the Hard value

             

            or should be something like that can't test

             

            =if(CURRENCY_PG='USD',AMOUNT*Only({<C_CURRENCY_A={'USD'},C_CURRENCY_B={'EUR'},CALENDARDATE={">=$(DATE)<=Today()"}>}N_FX_VALUE),0)