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

    Request help for right formula

    chin hong lai

      Hi all,

       

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

      Referring to below example:

       

      Tasks          
      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
          Jim Dehner

          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
            Diego Parker

            Hi Chin,

             

            Please find the example attached.

            Sheet 1.png

             

            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