Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
mgomezlor
Contributor III
Contributor III

Exchange Rates

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.

2 Replies
dionverbeke
Luminary Alumni
Luminary Alumni

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

zebhashmi
Specialist
Specialist

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)