☐ cool new feature  ☑ could help many users!  ☐ removes a „bug“  ☐ nice to have  ☐ nobody needs it     #myTop10_2022        There is a set of posts with questions how to restrict the Col ... aggregation functions to a specific subset.  Compute Column Mean while restricting observations used   JMP10: Col Max() and Col Min() functions returned the data from excluded row   Col Functions and Row States   How do I use the Col Maximum Formula with a "where" condition   Exclude data in formula      Application cases:   excluded rows from the aggregation:    Col Max(if(not(excluded()), :weight,.),: sex)  or even shorter (if one doesn't need values in the excluded rows);    Col Max(:weight, :sex, :excluded())    Benchmark some data with a reference data set (country A in this post)     :data / Col Maximum(if(:country="A", :data,.))         calculate relative performance over time (divide all values at time t>=t0  by values at t=t0)  (here the Col Mean is actually to get the value for :time=0 to the other rows.:)    : data / Col Mean(if(:time=0, :data, 0) :batch, :sampleID)        As one can see in the examples above, the easy solution is an If statement inside the Col xyz aggreagtion:      col Mean( if(:sex=="M", :weight,.), :age);
col Mean( if(not(excluded()), :weight,.), :age)         The problem:  The approach is EXTREMELY slow - if the subset if-condition contains a  string comparison:  How-do-I-use-the-Col-Maximum-Formula-with-a-where/M78599    Then the time scales quadratically with the number of rows and even for small data sets with just a few 1000 entries the calculation takes seconds.  For data sets with some million entries it would take months (!!!) - but will be stopped by a timeout and an error message pops up. Not nice!     My wish:   fix the bug     Even better:  Provide an additonal where option for Col xyz aggreagtions   which fixes the timing problem automatically: first restricts the data set, then applys the aggregation (--> much faster, even if the cool "col..." speedup has a  bug)  which  make subset definitions easier,  instead of (*), you just need:       col Mean(:weight, :age, where(:sex=="M"))
col Mean(:weight, :age, where(not(excluded())))         timing examples, copied from here      corrected version - in a prev. version the screenshot showed [ms] instead of [s]     more wishes submitted by     
						
					
					... View more