Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
PROGRAM | DATE | CURRENCY_PG | Amount | Amount € |
P1 | 2016-03-01 | PLN | 100 | 24,534 |
P2 | 2016-07-21 | USD | 285 | 260,76246 |
P3 | 2016-12-25 | EUR | 56 | 56 |
P4 | 2016-06-18 | GBP | 74 | 82,8985 |
P5 | 2017-03-15 | MXN | 346 | 15,04347796 |
P5 | 2017-08-24 | MXN | 458 | 19,91304308 |
P6 | 2017-04-10 | EUR | 12 | 12 |
P7 | 2018-10-31 | PLN | 432 | 100,8403344 |
P8 | 2018-11-04 | USD | 23 | 19,00041316 |
PIPELINE:
PROGRAM | DATE_PL | CURRENCY_PL | Commit | Commit € |
P1 | 2016-10-08 | USD | 89 | 73,5233379 |
P2 | 2016-12-21 | PLN | 285 | 66,5266095 |
P3 | 2016-03-18 | EUR | 56 | 56 |
P3 | 2016-12-25 | GBP | 78 | 87,3460247 |
P4 | 2016-11-05 | USD | 126 | 104,08922 |
P4 | 2017-04-26 | MXN | 258 | 10,8357833 |
P5 | 2018-08-24 | PLN | 685 | 159,89729 |
P6 | 2017-10-31 | GBP | 12 | 13,43785 |
P7 | 2018-11-30 | EUR | 80 | 80 |
P7 | 2018-12-28 | EUR | 132 | 132 |
P8 | 2018-12-11 | USD | 66 | 54,5229247 |
I attach the qvf.
Thank you very much in advance.
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
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)