I think your best bet would be to first join the two sheets. If you have not worked with joins before, this should help you: Join Your Data.
I would start with the Shipping Records sheet, then join in the Lead time sheet as a left join with Product Description=Product Description as the join clause. Now, in the joined data, you can create Target Ship Date as DATEADD('day', [Lead Time], [Last Credit Approval Date]) and Target Ship Date Variance as DATEDIFF('day', [Target Ship Date], [Actual Ship Date]).
is it possible to use joins for two separate data sources? In my example they are two excel files but in reality they are a oracle database and an excel file. Thank you for your help.
1 of 1 people found this helpful
Ah, now I understand why you had it set up this way. No, I don't believe that is possible.
However, going back to the workbook you posted with Product Description as the linking field, if you make Target Ship Date DATEADD('day', AVG([Lead Time]), ATTR([Sheet1 (Shipping Records)].[Last Credit Approval Date])) and Target Ship Date Variance DATEDIFF('day', [Target Ship Date], ATTR([Sheet1 (Shipping Records)].[Actual Ship Date])), then the calculations should work.
I just got a notification that you marked my answer correct, Chuck. Glad I was able to help!
However, as you may know, Tableau 10 was recently released and comes along with some pretty cool improvements. One of these updates is that it now allows you to join sheets from multiple databases into the same datasource! So if you have further need to combine the data from an Oracle database and Excel file, you can do this with a join in Tableau 10.