2 Replies Latest reply on Aug 14, 2019 4:25 AM by Diego Parker

# Request help for right formula

Hi all,

I have a calculation to be made regarding a working time of specific tasks.

Referring to below example:

Start Date and Time
End Date and Time
Job A13-Aug-2019 20:30:0013-Aug-2019 21:00:00

Job B

14-Aug-2019 01:00:0014-Aug-2019 02:00:00
Job C14-Aug-2019 03:00:0014-Aug-2019 03:30:00

I am required to calculate 3 figures.

1) Total time required to complete these 3 jobs

2) Time required to complete each job

3) End to End job timing for each job

I have encounter issues on calculating point 3, taking Job B for example, the end to end job timing should be 13-Aug 21:00 to 14-Aug 02:00

Is there a direct formula in Tableau that can allow me to do this subtraction?

Results = Data row (N) - Data row (N-1)

• ###### 1. Re: Request help for right formula

first

the start and end date/time must be cast as date/time

if not please post your book so we can see the data

then the formula you want is datediff('minute',[start date and time],[end date and time])

used as is it will return the time difference in minutes base on the individual job task (as in the viz)  then use grand total to total time

or  it can be used in an lod as in time lod=  {fixed [task:  sum( datediff('minute',[start date and time],[end date and time]))}

and that can be totaled for the end to end time as    [fixed :sum([time lod])}

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: Request help for right formula

Hi Chin,

I created the following formulas:

1) Difference ->  DATEDIFF("minute",[Start Date and Time],[End Date and Time])

2) End to End -> DATEDIFF("minute",LOOKUP(attr([End Date and Time]),-1),attr([End Date and Time]))

3) For the total I just added the Total using analysis->Total

Hope this helps. If does, please mark it as helpful/correct so other users can refer to it. Many thanks!

Best,

Diego