How to Count rows based on difference between values in a column ?

Ohad_s — Tue, 01 Oct 2024 04:33:00 GMT

Hi,

I am trying to create a new column based on column "DieY"

I want continuous count based on the difference between each row and the previous one.

row 2-row 1, row 3-row 2, row 4-row 3 and so on.

if the difference is below a certain value, e.g. 5, set the value to 1.

if the difference is above, add 1 to the count.

keep the last count until the difference is above the value set in the formula.

so eventually I will have a new column with values such as: 1,1,1,1,1,1,1,2,2,2,2,2,2,2,3,3,3,3,3,3,3 and so on..

Many thanks to any help

Re: How to Count rows based on difference between values in a column ?

txnelson — Tue, 01 Oct 2024 08:21:58 GMT

Here is the formula I came up with

If(
	Row() == 1, count = 1,
	:DieY - Lag( :DieY ) > 5, count
	++);
count;