Whether a table for exchange rate history is created in the database that has sales data, in Excel, or in Google Sheets, the approach should be similar.
The exchange rate table should have a field for MMYYYY, and a field for the exchange rate.
On the data Source tab for the sales connection, right-click on Sales Date and choose Create Calculated field; name it MMYYYY:
RIGHT('0' + STR(MONTH([Order Date])),2) + STR(YEAR([Order Date]))
LEFT JOIN from Sales to Exchange rates on the MMYYYY field. The calculated field from the original post now becomes:
[NIO value]/IFNULL([exchange rate],31)
This defaults to 31 when the exchange rate hasn't yet been populated; the calculation can be revised to use a better default.
Hello Eric, I'm stuck. My two data sources would be tableau server and google sheets, I couldn't execute for calculated field properly, can you attach a sample?
Thanks in advance,
My apologies, I am also unable to join on a calculated field. However, I verified that data blending works.
- On the data source tab, choose Data, new data source, and connect to the spreadsheet for exchange rates.
- Ensure that the calculated field for YYYYMM in the sales data source has the same name as the field identifying the exchange rate month.
- Make sure that the first field added to sheet1 is from the sales source, making that datasource primary.
- On the exchange rate data source, for the YYYYMM field, make sure that the "chain icon" is orange/connected.
- Blending combines aggregated data, the formula will look like: SUM([NIO Value])/IFNULL(AVG([ExchangeRate (DemoExchange)].[Rate]),31)
Exchange.twbx 50.5 KB
This works great.