Converting to a Base Currency from separate tables

[#1]

Hi Qlik Community,

Hope you can help, I am stumped with this one

I have three tables, two are from a SQL database and one is from a Excel Spreadsheet.

The two SQL tables have a relationship, No. but the Excel Spreadsheet does not as it is monthly exchange rates

SQL table 1 is called Orders with No., Date, Currency and Product

No.	Date	Currency	Product
8475481	19/11/14	GBP	1
8475482	21/11/14	EUR	1
8475483	05/12/14	USD	3
8475484	08/12/14	GBP	6

SQL table 2 is called Prices with No., and Price (each order may have multiple items)

No	Price
8475481	1000
8475481	2000
8475482	1500
8475483	1600
8475484	1700
8475484	1300
8475484	1600

Excel table is called ExchangeRates with Month, Year, Currency, Exchange rate

Month	Year	Currency	Exchange Rate
Nov	2014	GBP	1
Nov	2014	EUR	1.266
Nov	2014	USD	1.684
Nov	2014	GBP	1
Dec	2014	EUR	1.288
Dec	2014	USD	1.680

I made a key by concatenating fields in Orders

[Currency]&Month("Date")&Year("Date")& AS [ExchangeID]

I did the same with ExchangeRates

[Currency]&[Month]&[Year] AS [ExchangeID]

This will give me a link between Orders and ExchangeRates (There is one 1 rate per currency per month).

Now I can create a straight table with the No. Dimension with the following Expression

SUM([Price]) / [Exchange Rate]

This will give me a list of Orders all in the same currency

I tried this as a chart to give me a totals per month (in the default currency) as the dimension but it says 'No Data to display'

My aim is to create a chart that would show the base currency no matter what dimension is selected, and I am not sure how to achieve this.

Please can you help me.

Thank you in advance

Mark

[Anonymous]

is DD/MM/YY your default date format?

if not you need to format in Orders month and year like this:

[currency]%month(date#('19/11'14','DD/MM/YY')&year(date#('19/11/14','DD/MM/YY'))

[#3]

Hello Rudolf,

Thank you for your swift reply.

The Date format in the order table is DD/MM/YYYY (not shown in the Orders table, that was my mistake)