Problem with a column formula to assign production phases

SDF1

Hi All,

 

  I'm trying to write a column formula that will look at the difference in date between two rows and if >= than a certain value (in this case 4 days), then the column should decrement the phase value. If the date difference is <4 then it should keep the previous phase value. I've defined two local variables in the formula: phase and MinDaysbreak, which is the minimum days between phases (4). The column :"Date/Time:"n is the date column that the For() loop is iterating through.

  My formula looks like the following:

  And the JSL code for the formula is the following:

For( i = N Rows(), i >= 1, i--,
	If( i == N Rows(),
		phase,
		If( Abs( Date Difference( :"Date/Time:"n[i + 1], :"Date/Time:"n, "Day" ) ) >= MinDaysbreak,
			phase--,
			phase
		)
	)
)

  I'm not getting any errors for the formula, the formula is not suppressed, but the column is still empty. I still get nothing for the last row even if I remove the first else statement in the first If(), and the last entry for the column should be 9, as that's the local variable phase. I know that there are 9 phases.

 

  I'm not sure what I'm doing wrong, but I wouldn't be surprised if it's a simple mistake that I'm overlooking.

 

Thanks for any help!,

DS

ms2

The column is empty because the For() function does not return a value. Try pasting this code into the formula editor:

 

Local({i = N Rows(), phase = 9, MinDaysbreak = 4},
    For(i, i >= Row(), i--,
        If(i == N Rows(),
            phase,
            If(Abs(Date Difference(:"Date/Time:"n[i + 1], :"Date/Time:"n[i], "Day")) >= MinDaysbreak,
                phase--,
                phase
            )
        )
    );
    phase;
)

I think it will work as intended. Note these changes:

– The additional phase that sets the value of the current row (after been calculated within the For loop). Without this you get an empty column.

– To work correctly you should not iterate over all rows, only down to the current row using Row().  Otherwise you will get the same value for every row.

– You need an index for both dates to make sure to always compare with the next row. Without it is hard to predict the outcome (depends on the data).

 

I also defined initial i as a local parameter, but that's not necessary I think. 

 

However, it's quite unusual to use a for loop in column formulas. If you have a large data set it can be very inefficient to loop over the table in order to set the value for every row. There are other ways to solve this problem, with column formula or JSL.

/ms