Exchange Rates

[mgomezlor]

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.

[dionverbeke]

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]

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)