Calculate the production data within 24 hours timeframe.

[stj1120]

Hi all,

I have a production data. I'd need to find out the OEE1, OEE3, and MBTF values that came within 24 hours of the Actual Start and Actual Stop.

We should consider the timeframe between 'Actual Start' from 10:30 A.M of the current day until 10:30 A.M ('Actual Stop') of the next day.

The formulas to calculate the following KPI's:

OEE1 ==>> Valued Operating Time (mins) / Operating Time (mins)

OEE3 ==>> Valued Operating Time (mins) / Manned Time (mins)

MBTF ==>> Valued Operating Time (mins) / Number Of Stops 

The OEE1, OEE3, Valued Operating Time (mins), Manned Time (mins), and Number Of Stops values present in the excel sheet are the actual values as per the batch.

However, in order to achieve real values within 24 hours for OEE1, OEE3 and MBTF, we need to consolidate all the batches within this 24 hour period and find the values of Operating Time (minutes), Manned Time (minutes) and the Number of Stops using the Actual Start and Actual Stop values within 24 hours (10:30 a.m -- 10:30 a.m).. That’s where it was hard for me.

Please help me how to do this.

Thank you. 

Best Regards,

Teja.

[danilang]

Hi @stj1120 

Interesting question.  I can't help but think that I've seen something similar before.  Have you posted something similar before or is this a question for some kind of assignment or course?

In any case, to get started on this you have to break down the actual times into the amount that occurred on each effective date.  The effective date goes from 10:30 on day x to 09:59 on day x+1,  so 2020-07-01 10:30:00 to 2020-07-02 09:59:59 is all EffDate = 2020-07-01 10:30:00.  Calculate the start and end EffDate in a formula tool to use as the Start and End conditions in the Generate Rows tool mentioned below

Another point to note is that a stoppage only affects a batch if the batch has already started.  So if a stoppage starts at 01:00, but the batch starts at 02:00,  The effective stoppage start time is the batch start time.  The equivalent is applied to the stoppage end times as well.

Once these points are taken care of use a Generate Rows to generate one record for each effective date in the batch.  Then use a formula tool to calculate the amount of each event that gets applied to the current effective day.  You only have 4 conditions to check.  For the batch calculations the formula would be something like this

if [ActualStart]>=[EffDate] and [ActualStop] < DatetimeAdd([EffDate],1,"days") then
	//all today
	DatetimeDiff([ActualStop],[ActualStart],"seconds")  
elseif [ActualStart]<[EffDate] and [ActualStop] < DatetimeAdd([EffDate],1,"days") then
	//started before today but ended today
	DatetimeDiff([ActualStop],[EffDate],"seconds")
elseif [ActualStart]<[EffDate] and [ActualStop] >= DatetimeAdd([EffDate],1,"days") then
	//Started before today and ends after today
	86400
elseif [ActualStart]>=[EffDate] and [ActualStart] < DatetimeAdd([EffDate],1,"days") then
	//startedtoday and ends after today  
	DatetimeDiff(DatetimeAdd([EffDate],1,"days"),[ActualStart],"seconds")
else
    //Nothing happened today
	0
endif

If the event doesn't fall into the 4 first criteria, it gets 0.  Technically, you only need 3 of the 4 conditions for the batch calculation since it is contiguous, but you can use the same structure for the stoppages which have multiple start and end times within the batch.

Dan

[stj1120]

HI @danilang 

Thank you for providing the quick response.

I got this dummy production data from one of the blog related to OEE. I'm using this dataset for practise. I'm new to Alteryx. I'm learning the tool by practising the different datasets like this and whatever I don't know I'm posting the questions here in the community and learning slowly by looking at the solutions provided for my questions. 

I tried to find the solution but, didn't get the correct result.

If you don't mind, could you kindly help me with the workflow for the above scenario?

Thanks a lot!

Best Regards,

Teja.

[danilang · Accepted solution]

Hi @stj1120 

Here's that workflow that allocates the durations of the events to the various effective dates.  RunCurrDay is the amount of time that the batch had in the current effective date.  StopageCurrDay is the amount of the stoppage that applies to to the current effective date.  From here you can use these numbers to calculate the various metrics that you need and then summarize by EffDate.

Dan

[stj1120]

Hi @danilang 

Thanks a lot for the wonderful solution.

But, batch number shouldn't be included in the output. Whatever the batches that are produced during this 24 hours timeframe we need to consolidate all of them and  get the values for Valued Operating Time, Operating Time, Manned Time and Number Of Stops  during that 24 hour period. All these data values currently are shown in minutes as per batch in the excel sheet. 

How do we get these values?

Please check and help for this as well in the workflow. Sorry for asking you lots of questions.

Thank you. 

Best Regards,

Teja.

[danilang]

Hi @stj1120 

That's what I meant when I said "From here you can use these numbers to calculate the various metrics that you need and then summarize by EffDate."  I have no idea how Valued Operating Time, Operating Time, Manned Time are calculated.  Once you have these calculated use a summarize tool to count the number of stops per Effdate and perform the other summaries.  Once you summarize by EffDate, the batch numbers will no longer show up.  You'll have one record per EffDate.

Check out this Interactive Training video for more information on summarizing

Dan