4 Replies Latest reply on Nov 29, 2018 2:42 AM by Yuriy Fal

    Calculate time difference with interruptions

    dennis.fritz

      Hello Community,

       

      I'm new in using Tableau and need help for a new project.

       

      Target:

      Create a cycle time chart out of machine gained data.

       

      Problem:

      The calculation of cycle time.

       

      How can i calculate the time difference between "Part" in each row?

           If there is an interruption (jam, Part missing, Error) between two "Parts", then subtract the time between Start / End of the interruption.

       

      I have linked a Tableau .twbx file with the data.

       

      Thank you for helping!

       

       

      Here you can see an example of the database. Every station (A.B.C.D) has an own spreadsheet in the Excel-file.

       

      EventStart / EndDateStation
      PartReslut20.11.2018 07:00:00A
      PartResult20.11.2018 07:00:50A
      Part missingStart20.11.2018 07:00:55A
      Part missingEnd20.11.2018 07:05:00A
      PartResult20.11.2018 07:05:52A
      JamStart20.11.2018 07:05:55A
      JamEnd20.11.2018 07:07:00A
      PartResult20.11.2018 07:07:48A
      PartResult20.11.2018 07:08:55A
        • 1. Re: Calculate time difference with interruptions
          dennis.fritz

          Ok maybe we should reduce the complexity in the first step.

          Is it possible to calculate the time difference only between "Parts"?

          Interruptions don't have to be considered in the first step.

           

          Thanks for your help!

          • 2. Re: Calculate time difference with interruptions
            Yuriy Fal

            Hi Dennis,

             

            What you're trying to achieve is 'doable' in Tableau,

            but i have to stress my warning message here again --

            it couldn't be applied in any serious production environment,

            especially with tons of machine-generated data (like yours).

             

            Nevertheless, here it is -- using Table Calculations, of course.

            The main idea is to do RUNNING calculations all through,

            then capture the specific patterns in the Dimensions values

            to calculate the intermediates and the final values.

             

            Please find the attached.

            Hope it could help a bit.

             

            Yours,

            Yuri

             

            PS  The 'ultimate' solution would be implementing the same logic

            either in the DBMS of choice (using SQL-2003 compatible Window Functions),

            or right in the code (of the programming language) -- the choice is always yours.

            • 3. Re: Calculate time difference with interruptions
              dennis.fritz

              Hi Yuri,

               

              thanks for your support.

              The calculation works really well, great job.

              Now I have the problem, that i can't do any visualisation with the calculated fields.

              How can i do maybe a chart with the stations on X-axis and your calculated field YF: Datediff (Part - Begin/End)?

               

              Picture.JPG

              What do i have to do, that i can use the values you calculated in Sheet 7?

               

              Thank you for helping!

              • 4. Re: Calculate time difference with interruptions
                Yuriy Fal

                Hi Dennis,

                 

                Here is where the 'solution' complexity would take its' toll.

                For Nested Table Calculation to work as expected,

                one need all the necessary Dimensions on a view (somewhere).

                 

                For the simple layout you'd like to get, the [Station] is on Columns,

                and all other Dimensions (three of them from the original Sheet 7)

                should be on Details at least.

                 

                Besides, the Bars from the green pill on Rows should be

                the size of the Total (WINDOW_SUM) values for each Station.

                So one need yet another Nested Table Calc just for that.

                 

                And to leave only a single Mark for each Station on a view,

                one need a Table Calculation Filter just for that.

                 

                Last but not least, one should be careful about

                the proper Addressing for all the Table Calculations

                (both on a view and on filters). Your case it's rather simple one.

                All the (Nested) Table Calcs should have the same addressing --

                along all Dimensions excluding the [Station].

                 

                Please find the attached with mods.

                 

                Yours,

                Yuri