For Function

cjs05

Hello all,

 

This is likely pretty basic, but i am very new to JSL so be gentle!

 

How do i create a column that meets the following criteria.

 

For every Replenishment Order there is a lag on the inventory column until the inventory is positive. In this example it would pull 2/1/18 because the lag before that results in a positive number.

 

Type                           Week           Inventory               Lag

Stock                          1/7/18          10

Customer Order          2/1/18          -150

Customer Order          3/1/18         -250

Replenishment Order 4/1/18          500                         2/1/18

txnelson

Here is an example of using a formula for your Lag column that will produce the results you want

names Default to Here( 1 );
New Table( "Example",
	Add Rows( 4 ),
	Set Header Height( 44 ),
	New Column( "Type",
		Character,
		"Nominal",
		Set Values(
			{"Stock", "Customer Order", "Customer Order", "Replenishment Order"}
		),
		Set Display Width( 118 )
	),
	New Column( "Week",
		Numeric,
		"Continuous",
		Format( "m/d/y", 12 ),
		Input Format( "m/d/y" ),
		Set Values( [3598128000, 3600288000, 3602707200, 3605385600] )
	),
	New Column( "Inventory",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [10, -150, -250, 500] )
	),
	New Column( "lag",
		Numeric,
		"Continuous",
		Format( "m/d/y", 12 ),
		Input Format( "m/d/y" ),
		Formula(
			If( Row() == 1, holdval = . );
			If( :Inventory < 0 & Is Missing( holdval ) == 1,
				holdval = :Week
			);
			finalval = .;
			If( :Inventory > 0,
				finalval = holdval;
				holdval = .;
			);
			finalval;
		)
	)
)