topic Re: Creating Selection Aware Running Total in QlikView

Creating Selection Aware Running Total

Sun, 14 Jul 2013 23:56:19 GMT

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

Re: Creating Selection Aware Running Total

rwunderlich — Mon, 15 Jul 2013 04:41:07 GMT

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

Re: Creating Selection Aware Running Total

Mon, 15 Jul 2013 04:59:13 GMT

Thanks for your email.

I ll be attending to it shortly

Regards

Femi