4 Replies Latest reply on Mar 22, 2018 6:26 PM by swaroop.gantela

# DATEDIFF between two lines

I'm using this code below to try and send the results of the time between two dates and it's just sending back 0.

ZN(DATEDIFF('day',IF CONTAINS ([Status],'REC') = TRUE

Then [Date]END, IF CONTAINS ([Status],'DEL') =TRUE

Then [Date]END))

Seems simple right? Just tell me the difference between the two dates?

I have to be missing something here.

Thanks in advance for any help.

Simple code is:

DateItem Number
Status
1/22/2018123Recieved
1/24/2018123Delivered
• ###### 1. Re: DATEDIFF between two lines

Kirk,

I think that such calculations are done on a row level,

and so both the Received and Delivered dates would need to be on the same row

for that calculation to work.

One way to accomplish that would be to use level of detail calculations

(if those will work for your datasource and if they do not hamper performance).

The format of the LOD for [RecDate] would be:

{ FIXED [Item]:MAX(IF CONTAINS([Status],"REC") THEN [Date] END)}

which for a given [Item] will find the received date and fix that to every row that has that [Item] number.

Likewise for the [DelDate]:

{ FIXED [Item]:MAX(IF CONTAINS([Status],"DEL") THEN [Date] END)}

Because of the Fixing, both the Received Date and Delivered Date are on the same row,

so the subtraction can be made:

DATEDIFF('day',[RecDate],[DelDate])

Another method would be to use Lookup function to find the date in the previous line, for example.

• ###### 2. Re: DATEDIFF between two lines

Thanks so  much for this!

I’m working on it now as the larger dataset is a live feed from SQL server.

Kirk Barnett  | Manager, Support Services Systems

Tel: 706.763.5508 | Cell: 706.304.2650

kbarnett@aflac.com<mailto:kbarnett@aflac.com> | aflac.com

• A Fortune 500 Company

• Fortune’s Best Companies to Work For

• ###### 3. Re: DATEDIFF between two lines

Next steps in my Tableau adventure.

As I’m looking at SLA times is there a way to not include weekend and holidays when I use the datediff to see if it was longer than say 3 days?

Swaroop I want to thank you again for the reply as I had a few others looking into this with not luck but this did the trick for me!

Kirk Barnett  | Manager, Support Services Systems

Tel: 706.763.5508 | Cell: 706.304.2650

kbarnett@aflac.com<mailto:kbarnett@aflac.com> | aflac.com

• A Fortune 500 Company

• Fortune’s Best Companies to Work For

• ###### 4. Re: DATEDIFF between two lines

Kirk,

Here is one method describing the handling of weekends:

https://www.theinformationlab.co.uk/2017/05/22/calculate-working-days-dates-tableau/

Here is another version:

Calculating the Number of Business Days in a Month | Tableau Software

The first method was employed for your example in the attached workbook and seemed to work.

Here are more links on the subject: