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

Calculating Lag Days

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 Code Order Number Date Order Received Invoice Date Original Order Number Lag Days REG 1234 12/2/2017 1/1/2018 REG 1235 1/16/2018 2/15/2018 REG 1236 1/3/2018 2/2/2018 REG 1237 2/17/2018 3/19/2018 REG 1238 2/4/2018 3/6/2018 REPL 1239 3/21/2018 4/20/2018 1234 139 REG 1240 3/8/2018 4/7/2018 REG 1241 4/22/2018 5/22/2018 REPL 1242 4/9/2018 5/9/2018 1236 126 REG 1243 5/24/2018 6/23/2018 REG 1244 5/11/2018 6/10/2018 REG 1245 6/25/2018 7/25/2018 REPL 1246 6/12/2018 7/12/2018 1244 62 REG 1247 7/27/2018 8/26/2018 REG 1248 7/14/2018 8/13/2018 REG 1249 8/28/2018 9/27/2018 REG 1250 8/15/2018 9/14/2018 REG 1251 9/29/2018 10/29/2018 REPL 1252 9/16/2018 10/16/2018 1245 113 REPL 1253 10/31/2018 11/30/2018 1235 318 REPL 1254 10/18/2018 11/17/2018 1240 254

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

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

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

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

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.

1 of 1 people found this helpful