297442_en-US

liliconcom

Moving shifts / changes

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 ... 

AnalyticsOtherScriptingRe: Moving shifts / changes

I have always found that working code should be praised, not faulted, so if it is working.....use it.

Re: Moving shifts / changes

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;
,
.
)
)

Re: Moving shifts / changes

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 ... 

Re: Moving shifts / changes

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;

Re: Moving shifts / changes

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;