Calculate the production data within 24 hours timeframe.

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.

Book1.xlsx

Workflow

Transformation

Preparation

Accepted answers

danilang

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

ProdData.yxzp

All comments

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703