Create MTBF table with 12 months window forward and backward

[ahsansalik]

Hi I am trying to create a table for Mean Time Before Failure (MTBF) over a 12 month rolling window. My Problem is my faults end Dec 20 and after that i need to keep the window moving forward by subtracting data from faults per  months before 12 month window start and prior to Dec 20 I need to have cumulative sum of faults. Units represent the sum and subtraction before Dec 20.

Operating Hour	Months	Faults	Units	MTBF
175200	Aug-19	1	1	175200
175200	Sep-19	1	2	87600
175200	Oct-19	1	3	58400
175200	Nov-19	1	4	43800
175200	May-20	1	5	35040
175200	Jul-20	1	6	29200
175200	Aug-20	1	7	25029
175200	Sep-20	1	8	21900
175200	Oct-20	1	9	19467
175200	Nov-20	1	10	17520
175200	Dec-20	1	11	15927
175200	Jan-21	0	7	25029
175200	Feb-21	0	6	29200
175200	Mar-21	0	5	35040
175200	Apr-21	0	4	43800
175200	May-21	0	3	58400
175200	Jun-21	0	2	87600

[mceleavey]

Hi @ahsansalik ,

I don't think I'm following.

Could you post your expected outcome as well as the starting data? That would help us a lot.

M.

[ahsansalik]

@mceleavey Hi,
sorry i wasn't very clear with my query. i actually just want the focus on the middle 3 columns where the Faults are the happening each month and Units is a running sum of the faults until Dec 20. Now after Dec 20 there arent any faults so i am actually looking at a sliding 12 month window for my calculation of MTBF. So before Dec 20 all the faults are adding up so units are running sum of the faults. After Dec 20 no fault happened but since its a 12 month sliding window so Jan21 will be looking back upto Jan 20 so will not include any faults from 2019. As the months increase so does the sliding window is moving forward so Feb 21 will be upto Feb 20 and will not include faults before it. This is the data extract from a data source.

MTBF is = Operating Hour/ Units 

Operating Hour	Months	Faults	Units	MTBF
175200	Aug-19	1	1	175200
175200	Sep-19	1	2	87600
175200	Oct-19	1	3	58400
175200	Nov-19	1	4	43800
175200	May-20	1	5	35040
175200	Jul-20	1	6	29200
175200	Aug-20	1	7	25029
175200	Sep-20	1	8	21900
175200	Oct-20	1	9	19467
175200	Nov-20	1	10	17520
175200	Dec-20	1	11	15927
175200	Jan-21	0	7	25029
175200	Feb-21	0	6	29200
175200	Mar-21	0	5	35040
175200	Apr-21	0	4	43800
175200	May-21	0	3	58400
175200	Jun-21	0	2	87600

[mceleavey]

@ahsansalik ,

Are you saying you only have the first three columns and you would like the Units and MTBF columns to be calculated dynamically in Alteryx?

M.

[ahsansalik]

yes that's correct

[ahsansalik]

I was thinking of using generate rows tool for data after dec 20 as that where data ends.

[OllieClarke · Accepted solution]

Hey @ahsansalik 

I had a go at this, but got different answers. Why were you expecting Feb-21 to have 6 Units rather than the 7 faults which occurred since Feb-20?

(I've attached the workflow anyway, in case it helps)