Choose Language Hide Translation Bar
Highlighted
Bearcat
Level I

A cumulative sum that resets based on an event?

I'm trying to find a relationship between uses and part lifetime. This is a part that runs constantly when in use. I need a running total that resets when the part is replaced. In my case, I have the first four columns, I want to know how to create the "Jobs Part has Done" column that resets when a Repair event is detected.

DateTime Started

DateTime Ended

JobsEventJobs Part has Done
1/1/2019 8:00 AM1/1/2019 9:00 AM2Work2
1/1/2019 10:00 AM1/1/2019 12:00 PM5Work7
1/1/2019 3:00 PM

1/1/2019

6:00 PM

5Work12

1/1/2019

6:00 PM

1/1/2019

7:00 PM

 Replacement0
1/1/2019 8:00 PM1/1/2019 9:00 PM2Work2

1/1/2019

10:00 PM

1/1/2019

11:00 PM

2Work4
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
vince_faller
Super User

Re: A cumulative sum that resets based on an event?

This seems to work for me.  Might not be the most efficient. 

 

Names default to here(1);
dt = New Table( "ColCumSum with Reset",
	Add Rows( 6 ),
	New Column( "DateTime Started",
		Numeric,
		"Continuous",
		Format( "m/d/y h:m", 19 ),
		Input Format( "m/d/y h:m" ),
		Set Values(
			[3629174400, 3629181600, 3629199600, 3629210400, 3629217600, 3629224800]
		)
	),
	New Column( "DateTime Ended",
		Numeric,
		"Continuous",
		Format( "m/d/y h:m:s", 23, 0 ),
		Input Format( "m/d/y h:m:s", 0 ),
		Set Values(
			[3629178000, 3629188800, 3629210400, 3629214000, 3629221200, 3629228400]
		)
	),
	New Column( "Jobs",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2, 5, 5, ., 2, 2] )
	),
	New Column( "Event",
		Character( 16 ),
		"Nominal",
		Set Values( {"Work", "Work", "Work", "Replacement", "Work", "Work"} )
	),
	
);

dt << New Column( "Jobs Part has Done",
	Formula(
		If(
			Row() == 1, :Jobs,
			:Event == "Replacement", 0,
			Lag( :Jobs Part has Done ) + :Jobs
		)
	)
);
Vince Faller - Predictum

View solution in original post

1 REPLY 1
Highlighted
vince_faller
Super User

Re: A cumulative sum that resets based on an event?

This seems to work for me.  Might not be the most efficient. 

 

Names default to here(1);
dt = New Table( "ColCumSum with Reset",
	Add Rows( 6 ),
	New Column( "DateTime Started",
		Numeric,
		"Continuous",
		Format( "m/d/y h:m", 19 ),
		Input Format( "m/d/y h:m" ),
		Set Values(
			[3629174400, 3629181600, 3629199600, 3629210400, 3629217600, 3629224800]
		)
	),
	New Column( "DateTime Ended",
		Numeric,
		"Continuous",
		Format( "m/d/y h:m:s", 23, 0 ),
		Input Format( "m/d/y h:m:s", 0 ),
		Set Values(
			[3629178000, 3629188800, 3629210400, 3629214000, 3629221200, 3629228400]
		)
	),
	New Column( "Jobs",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [2, 5, 5, ., 2, 2] )
	),
	New Column( "Event",
		Character( 16 ),
		"Nominal",
		Set Values( {"Work", "Work", "Work", "Replacement", "Work", "Work"} )
	),
	
);

dt << New Column( "Jobs Part has Done",
	Formula(
		If(
			Row() == 1, :Jobs,
			:Event == "Replacement", 0,
			Lag( :Jobs Part has Done ) + :Jobs
		)
	)
);
Vince Faller - Predictum

View solution in original post

Article Labels

    There are no labels assigned to this post.