Creating Selection Aware Running Total

[#1]

Hi All,

I have a somewhat difficult challenge. The attached excel contains 2 tables. transaction & Exrate tables.

Each transaction is stamped with a given currency. The amount per line must be multiplied by the appropriate exchange rate to get the local amount. ie each line will have a calculation of Amount * ExRate.

My main challenge is how to pick the ExRate based on the transaction date. eg, S/No 1 in the excel sheet is a USD transaction on 19 Apr 2007. The correct exchange rate at this date is 31 Mar 2007 with a value of 158.96.

How do I create a pivot/straight table to show this results?

TIA

Femi

[rwunderlich]

This is a common problem, usually solved with IntervalMatch. You can find a good discussion here, uisng a JOIN.

http://community.qlik.com/message/314959#314959

I typically use IntervalMatch. You can find an example of the IntervalMatch solution in the QV Cookbook available at http://robwunderlich.com/downloads.

Also a good technical bried by Henric here

http://community.qlik.com/docs/DOC-4310

-Rob

[#3]

Thanks for your email.

I ll be attending to it shortly

Regards

Femi