4 Replies Latest reply on Sep 30, 2016 12:57 PM by Benjamin Greene

    Shipment Lead Time calculation

    Chuck Blechle

      I am trying to add [lead time] (integer in days) to [Last Credit Approval Date] date to determine the [target ship date].  I am then trying to get the [Target Ship Date Variance] by [target ship date] - [Actual Ship Date].  My error is All data must be aggregate or constant.  Attached is the .twbx

        • 1. Re: Shipment Lead Time calculation
          Benjamin Greene

          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]).

          • 2. Re: Shipment Lead Time calculation
            Chuck Blechle

            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.

            • 3. Re: Shipment Lead Time calculation
              Benjamin Greene

              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.

              1 of 1 people found this helpful
              • 4. Re: Shipment Lead Time calculation
                Benjamin Greene

                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.