topic Re: How do i sum up number of consecutive values within the same column? in Discussions

How do i sum up number of consecutive values within the same column?

yew1992 — Thu, 14 Nov 2024 07:19:55 GMT

Hi,

I was able to identify consecutive values on a column "Data" (Refer to " sequence roll" column). However i need to generate a column (refer to Expected Result column) to sum up the number of consecutive values.

Any help or advice will be appreciate. Thank you

" sequence roll" column

If( :Item == Lag( :Item, 1 ),
If( Dif( :Data, 1 ) == 1,
Dif( :Data, 1 ), .
), .
)

Re: How do i sum up number of consecutive values within the same column?

yew1992 — Thu, 14 Nov 2024 07:20:43 GMT

Re: How do i sum up number of consecutive values within the same column?

jthi — Thu, 14 Nov 2024 07:29:13 GMT

Is there a specific reason of dropping out the value from first matching row while still adding it to the calculation?

Here you have 5 consecutive values but first row is set as missing.

Re: How do i sum up number of consecutive values within the same column?

jthi — Thu, 14 Nov 2024 07:33:29 GMT

I would create two columns, Sequence with formula such as

As Constant(seq = 0);
If(Dif(:Data) != 1, 
	seq++
);
seq;

and then use that for calculations using Col Number

Col Number(:Sequence, :Sequence)

If you want to make first row missing, you can do that by adding if statement to result

If(Row() == Col Min(Row(), :Sequence),
	.
,
	Col Number(:Sequence, :Sequence)
)

Re: How do i sum up number of consecutive values within the same column?

yew1992 — Thu, 14 Nov 2024 09:03:54 GMT

Hi,

No specific reason. Just that my formula to check sequence roll is depending on the previous row above.

Re: How do i sum up number of consecutive values within the same column?

yew1992 — Thu, 14 Nov 2024 09:11:00 GMT

It works!

Thank you :)