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

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; ... View more

Jim  This worked great.  Only small issue is a warning w/ the use "Current Data Table" within a formulae.  JMP throws a warning stating to use an Eval or Expr Eval function instead.  I would likely activate and then deactivate this function after its done calculating.    This has been great help. Thanks so much.    ... View more

Seeing your other answer on the linear slopes, I got  another idea of where to put the index for MK but did not work either.  attached in my latest w/ that 5th attempt ... View more

Hello JMP team ...  Nelson's approach is very likely what I was looking for. I am conversing w/ him on a similar need with mann kendall tau test.    My input is not in a single row.    My data is typically contains several parameters each measured at a different time step. Tidy data format; parameters are in columns, each row is a different time state - i.e. chronology is in rows.    I am looking into many statistical functions measuring trend in one of these or a fit versus some other X in time but as a function of rolling windows. So instead of regressing the whole history of X vs Y for instance, I like a logic that looks into regression of the two within constant past N time steps. Each row then re-evaluates and gets me a new result. These could a slope from linear regression, or a tau test from a non-parametric MK test or some other stat output.    Many times, we do not know when the correlation between the X's have changed, so this is one attempt to look into the impact of time for any analysis at hand. N mentioned above can be changed to various values to examine short term vs mid/long terms trends.   I am ok w/ scripts, they are not my preference unless the computations can great benefit a method offered only by scripts. My preference is generally to put the calculations into formulaes and have them maintained within the data frames.  Until today, I did not know that script structures could be deployed within formulaes.  That probably was the main cause of my confusion.    Appreciate all your help.  ... View more

hello  Thanks for this great answer. This was actually a revelation for me because this showed that a formulae can contain script like structures, which was unknown to me .  I accepted your solution but do need your help one more time. Uploading a sample data set.  My need of MK tau is on rows for  a single column. That is actually correctly done (my adaptation from your prior help) in col Tau_WholeData.  What I am really trying to do is to get a trend of a single parameter in timesteps (temporal trend if you will).  So I am trying to set a moving (rolling) window of size 10, start the window at row 10, and then move it by one record (row) - each time calculating MK Tau of the prior 10 rows .    I did similar things w/ Index function (e.g. Index( Row() - 9, Row() )) for 10 rows and I tried 3-4 different options of placing this into the  Tau_WholeData formula - so far no success.    I appreciate if you could let me know how I should change this.  thanks ...      ... View more

I am not looking for a script. I am looking for a formula application (i.e. using formula builder) ... View more

hi ...    You mentioned " Linear Regression() function, which could be used in a formula" ...and getting a slope from a formula fitting to provided data set being the final objective, where would one go find Linear Regression() function or any help ? Do you have any example ?    thanks ... View more

Jeff  Your modification on Ian's original solution worked perfectly. I can now extract random data w/ a prescribed distribution. It would be nice to see this as an option under Table Subsetting ... My problem is 100% solved.    thanks for your support. ... View more

Ian I was aiming to get ~46K of sub-sample data, of which 44K have been duplicates. Could you pls write me that line for "bv" for your suggestion ?   thx again.  ... View more

Ian,  Forgot to ask one question. Is your selection done uniquely from original table or does this have a chance to have duplicate selections into the sub-table ? thx ... View more

Ian,  This is brilliant. I sort of thought of a similar approach but was unable to do so due to my basic script knowledge.  I was able to adapt it to my data and am getting what I want. I may perhaps expand this to 2-parameter distribution selection which I should be able to do from your script.    thanks for your support. ... View more

Jeff,  Thanks for your effort.  Your suggestion had a slight chance. I applied it to my data and unfortunately since the selection is random, it did not work. I ran it for about 3000 iterations and stopped it after 10 hours. The movement on the mean of data was insignifcant.    This isn't a simple technique (unless your name is Ian) .. I would imagine some sort of optimizer being involved trying to random pull from the direction that it makes more sense. GA's may be an overkill but certainly can do this kind of optimization.    From statistical perspective, this is perfectly ok for what I do. The 400+K represents a big data of assets.. I am trying to find assets that are similar to a sub-group under investigation.    thanks again. ... View more

Jim, thanks for your suggestion.    But your proposal does not have a way to force select according for the given distribution. So it wouldn't produce what I am looking for. To validate, I did follow the steps and it produced a dist very similar to the original one.    Maybe I am not explaning in a clear way.    I have 400k+ rows X N columns of data (huge matrix) . If I call one of N params, as "X" for argument sake.  X's distribution from this data - call it X.dist1.    I have a second dataset which has Xagain, and this time w/ X.dist2  X.dist1 is not too far but is different than X.dist2.    Given the fact that I have plenty of data in the first dataset (400k+), I am hence trying to sub-select data set from 1 so that selected data X distribution is the closest I can get to X.dist2.    Hence I was looking for some capabiltiy under Table - Subset where I can specify normal distribution characteristic OR using a formula and then using Table- subset.   for the formula, I put densities of dataset1 against my desired X.dist2.  But now I need a  uniform random way of sampling from the formulae outputs so to force the outcome distribution to be ~ X.dist2   OR any any other way ...   If I am to use a Table-Subset - the solution needs to specify a normal distribution from a parameter to pull from.  Or otherwise, I need to find a uniform random selection from a parameter (formula approach).      ... View more

My real goal is to compare N (other) parameters from two data sets already available, I am not trying to create a fictitious distribution. (Maybe I am misunderstanding your proposal). The given distribution is a constraint on an important parameter that may influences others. I am trying to select assets w/ similar distribution from "data" to a given distribution so to minimize the impact of this influential parameter. In this context, "distribution" only applies for a single parameter, and not multiple. So bottom line, I need to sub-select from 400K+ rows a number of sub-samples so that the distribution for the selected sub-sample is as close as possible to the given distribution. ... View more

Peter .. thanks for your suggestion. but that does not seem to work. My original data's mean/stdev are: 12816 and 1726 from a very large sample (400K +). I am trying to downselect a sub-sample that is closer to 14300 and 2050 I tried your suggestion on the formula (i.e. densities of the data wrt 14300, 2050) and unless I can force this random dist to be uniformly selected among the densities, I don't think I can get what I need. In fact, sub-sampling came out as expected w/ the original distribution. ... View more

Hi, Is there any plan to put slope and intercept into formula builder of a future JMP version w/ a more straight forward direct function ?  thx. ... View more

Brady,  Below script which is a slight modification from yours and should be the starting point .(w/ some minor modification on stdev being compared to a threshold). As you know, I need an actual script to further progress as this deletion would be done automatically by the script. The next phase internal code – after JMP data selection - is particularly having an issue when zero variance is encountered. Your prior version (ie. below) proved that these can be taken out. I am though trying to avoid deleting parameters w/ mean differences but with zero variance.   I cannot use a threshold that is dependent on actual parameter mean & distribution. e.g. I cannot specify practical difference, as this requires in depth examination of all 2000 parameters, not feasible. Not sure if I mentioned, but 2000 is just the tip of the iceberg, more coming. However, a single dataset contains roughly 2000 parameters.    I can use p_value to filter parameters in or out depending on which ones cause a failure in our internal code. I think from that stand point, t-test may be a better option. However, I do not know how to insert the t-test and extract a p_value .... so I can add it as an additional filter.    I feel like I have two options ...  1) Either I will use the code below as is w/ no further 2-sample means comparison. It would delete all such cases if even means are different. I need to then record the column names of those that were deleted (need help w/ that) , so that at the end of the overall study, I can turn my attention to them and analyze them in jmp to detect means difference if any. This would be a much smaller set of parameter analysis, percentage-wise, I expect zero variance cases to be a very minimal portion of the dataset.   2) Or my second option is to add a t-test or any other test which offers a normalized threshold to fine tune the population of significant differences. Eg. I can use p_value to confirm or reject significance.   What do you think ? thanks    -----------------------------------------------------------------------------------------------------------------  Names Default To Here( 1 ); dt = current datatable(); dtcol1 = column(dt, 1); dt1 = dt << subset(rows(dt<<get rows Where(ascolumn(dtcol1) == dtcol1[1])), invisible, selected columns(0)); dt2 = dt << subset(rows(dt<<get rows Where(ascolumn(dtcol1) != dtcol1[1])), invisible, selected columns(0)); // Get all continuous data columns colList = dt << get column names( numeric, continuous ); // Loop across all columns and find those with no variance  For( i = N Items( collist ), i >= 1, i--, If( (Col Std Dev( Column( dt1, colList[i] ) ) ) >= 0.01 & Col Std Dev( Column( dt2, colList[i] ) ) >= 0.01, colList = Remove( colList, i, 1 ) ) ); // Delete the columns with no variance dt<<delete columns(colList); close(dt1, nosave); close(dt2, nosave);   note: the thresholds on stdevs are not settled down. 0.01 is just one example. Will fine tune that.  ... View more

thanks Duane.    I am trying to determine within the script via specific outputs and comparisons if the parameter is different wrt classes A and B. I am not trying to analyze an intermediate table of results to then take the decision.    I've got 2000 parameter columns in a given dataset and many datasets of comparable size.    The procedure needs to auto-delete the related columns found as nearly the same between the two classes A and B, for a given parameter.    I initially wrote this down as zero variance seeking logic but the issue is that two parameters w/ zero variance but with different means should not be deleted. That's when I started changing the logic to include a test for comparing the 2 sample mean test.    My problem is I am very novice at scripting and do not know how to call out a parameter from the results of a given platform.  My other issue in our data is where you specified all parameters in the script, doing some like this even could prove to be very time consuming (maybe there is a easy enough way).    Brady's script actually is nearly perfect except I need to add a 2-sample test for means and know how I can use its output as an additional filter.    thanks for your help. ... View more