4 Replies Latest reply on Jan 24, 2019 2:04 PM by Jim Dehner

    Calculating Lag Days

    Nathan Bauer

      Hi everyone, I am wanting to create a calculation that will give me the Lag days (total number of days) of when a replacement order was received from the original order invoiced date.

       

      So, below is an example of what the data set looks like in excel and I can get a calculation to work in Excel, I am struggling to replicate this calculation in Tableau. I am trying to do this without duplicating my data source and blending the data.

       

      IF ORDER CODE = 'REPL' THAN LOOK UP THE ORIGINAL ORDER NUMBER IN THE ORDER NUMBER COLUMN AND RETURN THE INVOICE DATE, THEN CALCULATE THE DIFFERENCE BETWEEN THE DATE ORDER RECEIVED AND THE INVOICE DATE = LAG DAYS.

       

      =(IF(E24="REPL",LOOKUP(I24, ORDER NUMBER RANGE,DATE ORDER RECEIVED RANGE))-H24)*-1

           

      Order CodeOrder NumberDate Order ReceivedInvoice DateOriginal Order NumberLag Days
      REG123412/2/20171/1/2018
      REG12351/16/20182/15/2018
      REG12361/3/20182/2/2018
      REG12372/17/20183/19/2018
      REG12382/4/20183/6/2018
      REPL12393/21/20184/20/20181234139
      REG12403/8/20184/7/2018
      REG12414/22/20185/22/2018
      REPL12424/9/20185/9/20181236126
      REG12435/24/20186/23/2018
      REG12445/11/20186/10/2018
      REG12456/25/20187/25/2018
      REPL12466/12/20187/12/2018124462
      REG12477/27/20188/26/2018
      REG12487/14/20188/13/2018
      REG12498/28/20189/27/2018
      REG12508/15/20189/14/2018
      REG12519/29/201810/29/2018
      REPL12529/16/201810/16/20181245113
      REPL125310/31/201811/30/20181235318
      REPL125410/18/201811/17/20181240254

       

      Let me know if what I stated above makes any sense or if you would like me to send or give more insight as to what I am trying to do!

       

      I appreciate all of the help!

       

      Message was edited by: Nathan Bauer

        • 1. Re: Calculating Lag Days
          Jim Dehner

          Nathan

          I don't have your twbx workbook with the data attached - we like to see that to see the number of data sources and how the data nad viz are structured

          but you would solve this issue with lod expressions determining the difference between  the min date for the order number when the order code was REG and the max date for the order number when the order code was "Repl"

           

          JIm

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Calculating Lag Days
            Nathan Bauer

            I posted a sample set of the data that I am looking at. Would you be able to demonstrate the logic here? I think there may be an added complexity because the data source shows every transaction down to the line item.

             

            Thank you so much!

            • 3. Re: Calculating Lag Days
              Nathan Bauer

              To follow up with this question/ provide more explanation of the data. A REG (Regular) order number transaction will take place. A customer may call and we decide a REPL (Replacement) order number is needed to fix/ repair or replace an item on the REG (Regular) order number. The REPL (Replacement) order number is a completely new sequencing number. We have a second field that is labeled Original Order which allows us to see or be able to reference what the REG (Regular) order the replacement is for.

               

              I am looking to see the "Lag" days between the invoice date of the original order took place compared to the date we received the REPL (Replacement) Order.

               

              I hope that helps explain what I am trying to accomplish.

               

              Thanks!

              • 4. Re: Calculating Lag Days
                Jim Dehner

                Hi

                Little more complicated that I thought initially

                 

                See the attached - I copied the data and then joined the original and copy

                so that the order number = original order number in the copy

                 

                Then the  invoice date on the first order is

                and the order date on the Replacement order is

                 

                 

                and the lag is

                 

                 

                I filtered the data on order code in the separate copies of the data

                 

                Jim

                 

                If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.