nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Moving shifts / changes

altug_bayram

Hello ...

Back to my favorite topic at this board.

I need help w/ constructing following logic.

Within a moving window of constant size through data (Y) that is chronologically ordered against some datetime parameter

1-Need to find Y minimum =min

2-Need to find Y maximum = max

3-Compute shift = max - min (if max was identified chronologically after min's record)

or else

shift = min- max (if max was identified chronologically before min's record)

I can do 1 and 2 within a formula but couldn't find any trick to compute the shift

formula solution is preferred but if not a script would work too. (especially if the script can work within formula window)

note: I have some urgency to calculate these tomorrow (9/2/2020), any help would be appreciated.

thx ...

Accepted answers

txnelson

I think this will give you the shift value. Just change the constant size(conSize) value, and the Y column name(Y) in the formula and I believe it will calculate what you want

If( Row() == 1,
	conSize = 5;
	allVals = :Y << get values;
);
If( Row() <= conSize,
	start = 1,
	start = Row() - conSize
);
subMat = allVals[Index( start, Row() )];
minVal = Min( subMat );
maxVal = Max( subMat );
minLoc = Loc( subMat, minVal )[1];
maxLoc = Loc( subMat, maxVal )[1];
If( maxLoc >= minLoc,
	theShift = maxVal - minVal,
	theShift = minVal - maxVal
);
theShift;

altug_bayram

Jim

I believe I am all done w/ my logic w/ the following form (which isn't as compact as yours)..

My Y is an exponential weighted function, so the first term is blank which is my I need "+1" in

AssetRecordNumber == Col Number( :Name( "Y" ), :ASSET ) + 1

thanks for your great support in this. let me know if you see anything wrong but I have tested it and it seems to work to all 3 scenarios.

--------------------------------------------------------------------------

If( Col Number( :Name( "Y" ), :ASSET ) >= :detection_window & :AssetRecordNumber >= :detection_window,
If( :AssetRecordNumber == :detection_window,
conSize = :detection_window;
allVals = :Name( "Y" ) << get values;
);
start = Row() - (:detection_window - 1);
subMat = allVals[Index( start, Row() )];
minVal = Min( subMat );
maxVal = Max( subMat );
minLoc = Loc( subMat, minVal )[1];
maxLoc = Loc( subMat, maxVal )[1];
If( maxLoc >= minLoc,
theShift = maxVal - minVal,
theShift = minVal - maxVal
);
theShift;
,
If( Col Number( :Name( "Y" ), :ASSET ) >= :window_min & :AssetRecordNumber == Col Number( :Name( "Y" ), :ASSET ) + 1,
(conSize = Col Number( :Name( "Y" ), :ASSET ) ; allVals = :Name( "Y" ) << get values);
start = Row() - (Col Number( :Name( "Y" ), :ASSET ) - 1);
subMat = allVals[Index( start, Row() )];
minVal = Min( subMat );
maxVal = Max( subMat );
minLoc = Loc( subMat, minVal )[1];
maxLoc = Loc( subMat, maxVal )[1];
If( maxLoc >= minLoc,
theShift = maxVal - minVal,
theShift = minVal - maxVal
);
theShift;
,
.
)
)

All comments

txnelson

I think this will give you the shift value. Just change the constant size(conSize) value, and the Y column name(Y) in the formula and I believe it will calculate what you want

If( Row() == 1,
	conSize = 5;
	allVals = :Y << get values;
);
If( Row() <= conSize,
	start = 1,
	start = Row() - conSize
);
subMat = allVals[Index( start, Row() )];
minVal = Min( subMat );
maxVal = Max( subMat );
minLoc = Loc( subMat, minVal )[1];
maxLoc = Loc( subMat, maxVal )[1];
If( maxLoc >= minLoc,
	theShift = maxVal - minVal,
	theShift = minVal - maxVal
);
theShift;

altug_bayram

Jim,

Thanks again for prompt great support (as it's always been in the past)

I made 2 changes ...

1) detection_window is my table variable defining the rolling window length

2) I think start should be detection_window -1 (in second line)

More importantly, my Y column also needs to be pulled in (around "get values" stage ) per a second asset variable Z

(i.e. Y and Z are two columns, computing min max computed shift as a function of asset Z is needed. )

Now I plotted this against one specific Z asset and results are correct.

I do have an IF statement in the beginning that starts the calculation when we have number of rows = detection_window independently for each asset Z ... I think this is why it works.

[row()- (detection_window -1) ] starts from the correct beginning for each asset.

start =1 confused me a bit .. hope this does not start the window from row #1.

Appreciate your help.

If( Row() == 1,
conSize = :detection_window;
allVals = Y << get values;
);
If( Row() <= conSize,
start = 1,
start = Row() - (:detection_window - 1)
);
subMat = allVals[Index( start, Row() )];
minVal = Min( subMat );
maxVal = Max( subMat );
minLoc = Loc( subMat, minVal )[1];
maxLoc = Loc( subMat, maxVal )[1];
If( maxLoc >= minLoc,
theShift = maxVal - minVal,
theShift = minVal - maxVal
);
theShift;

altug_bayram

Jim, one more request on this.

My analysis started as a constant rolling window first but then I have to take into account some assets that do not have the targeted number of records (as in detection_window), so I need to relax my constraint for them by inserting a second table variable window_min (minimum of rolling window size).

so as an example, if my detection_window = 30 and window_min=15, that means

1) all assets having more records than 30, will have a rolling window of size 30

2) all assets w/ records between 30 -15 will have a single window of their max length

3) all other assets smaller than 15 will not have anything

could you pls make a modification recommendation from your original solution ?

thanks ...

altug_bayram

Jim

I believe I am all done w/ my logic w/ the following form (which isn't as compact as yours)..

My Y is an exponential weighted function, so the first term is blank which is my I need "+1" in

AssetRecordNumber == Col Number( :Name( "Y" ), :ASSET ) + 1

thanks for your great support in this. let me know if you see anything wrong but I have tested it and it seems to work to all 3 scenarios.

--------------------------------------------------------------------------