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

# Calculate time difference with interruptions

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

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.

• ###### 2. Re: Calculate time difference with interruptions

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.

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

Hi Yuri,

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)?

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

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