Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
grajmca_sgp123
Creator
Creator

exchange rate calculation on Master calendar table

Hi All,

I have two tables Orders and Currency table. Orders values are in local currency format, I want to convert this local currency to USD format by using Currency table. Here below are the sample tables

Orders:  we have values for every date

OrderDateSalesLocal Currency
6/15/2014100EUR
8/22/2014120EUR
9/14/201480EUR
11/11/2014200EUR
12/2/2014150EUR
1/14/2015300EUR
2/28/201550EUR
6/25/201570EUR
8/15/2015100EUR

Currency table: we have exchange rate values for every quarter.

From DateCurrencyExachange rate
6/2/2014EUR0.55
9/1/2014EUR1.24
12/1/2014EUR0.00123
3/1/2015EUR0.34
6/1/2015EUR0.5

I want to join these tables based on From Date and Order Date fields, but I do not have all FromDate values in Currency table  so we need to create master calendar data for From Date. I am fine with Master Calendar table I know how to create it but my requirement is ,if OrderDate fall between FromDate quarter dates i need to pick that date related exchange rate and multiple with sales.

Example:

OrderDateSalesLocal Currency
6/15/2014100EUR
8/22/2014120EUR
9/14/201480EUR
11/11/2014200EUR
12/2/2014150EUR
1/14/2015300EUR
2/28/201550EUR
6/25/201570EUR
8/15/2015100EUR

for example in above table first order(6/15/2015) fall between 6/2/2014 and 9/31/2014(From date field),So i need to pick exchange rate related to FromDate quarter and calculate sales like Sum(100 * 0.55)

For 3rd order(9/14/2014) fall between 9/1/2014 and 12/1/2014. So sum value is Sum(80*1.24)

same way for other date also.



output looks like this.


OrderDateSales
6/15/2014100*0.55
8/22/2014120*0.55
9/14/201480*1.24
11/11/2014200* 1.24
12/2/2014150*0.00123
1/14/2015300* 0.00123
2/28/201550* 0.00123
6/25/201570*.05
8/15/2015100*.50



please let me know your inputs here,Thanks in advance

10 Replies
grajmca_sgp123
Creator
Creator
Author

excellent Sergey you are Genius Thank you so much for your hard work