Hope the description below helps describe my problem - but in a nutshell, I have a calculated filed derived from variables in two different datasources (blended) and that derived field is not accurate. I am certainly new to Tableau but have used datameer, excel and SAS extensively - so you have some context on where I am coming from.
Data: See attached tableau which takes data from the attached CSVs
Goal: Sum of credit by month after converting the amount in credit column to USD (foreign exchange/forex conversion)
What I did in the attached workbook:
1) Imported each CSV as its own data source (I have to rely on calculated fields to join, so have to use separate datasources and then do data blending vs using a join on the datasource tab)
2) I have forex data at beginning of each month, so have to derive calculated fields on the sample data in order to join on the month and year. So created month and year fields in each of the data source
3) Join through Data -> Edit Relationships for month, year and currency
4) Now created a credit_usd calculated field to convert the credit column to a credit in USD column. credit_USD = credit*FX_Value (Tableau is forcing me to use aggregate functions as the variables for this calculated field are coming from different datasources)
5) When I summarize the data, the summation of the credit_USD from tableau is not matching up with similar analysis on excel
6) To trouble shoot I filtered data ONLY for "currency=usd" and since forex_value is 1 for all usd values, I expect sum(credit) to be equal to sum(credit_usd) - which is not the case
What am I doing wrong that the credit_USD is not represnting the accurate value?
I am open to other ways of doing this PLUS can also use some education on what went wrong with my approach