topic Re: Formula that counts the number of rows a given distance away with a specific value in Discussions

Formula that counts the number of rows a given distance away with a specific value

Shawn_Noland — Thu, 20 Nov 2025 05:52:36 GMT

I'm trying to develop a column function that will count every row of my data table where

1) The :X and :Y coordinates are less than 2 Euclidean distance away
&
2) The :Value is = 5

In this 5 by 5 grid every :Value is set to 1, except for the central datapoint (3,3) which has :Value = 5.

The datapoints in blue are <= 2 unites away from the central datapoint and thus get correctly count 1 datapoint in their range.

The datapoints in red don't have any rows with :Value = 5 in range and thus count a value of zero.

I've been experimenting with different methods to do this. Mostly trying out the For Each Row() command, but that doesn't seem to work well in a column function. I'm probably doing it wrong. Any ideas?

Re: Formula that counts the number of rows a given distance away with a specific value

jthi — Thu, 20 Nov 2025 06:23:45 GMT

If you need a formula it can get a bit slow but using As Constant can help. You could take a look into Distance(), KDTable() and maybe VPTree().

These are not optimized but are some simple examples on how you can use As Constant() and KDTable/Distance

Current Data Table() << New Column("R", Numeric, Ordinal, Formula(
	As Constant(
		tree = KDTable(Current Data Table()[0, {"X", "Y"}]);
		{rows, dist} = tree << K nearest rows(25, 13);
		v = rows[Loc(dist <= 2)];
	);
	If(Contains(v, Row()),
		1,
		0
	);
));

One example using Distance

Current Data Table() << New Column("R", Numeric, Ordinal, Formula(
	As Constant(
		five_idx = Where(:Value == 5);
		m = :X[five_idx] || :Y[five_idx];
	);
	Sqrt(Distance(Matrix({:X, :Y})`, m)) <= 2;
));

If you do not need formula, these can be made more efficient