Subtract rows from first row

Hi there,
I have a grouploop like this


What i need to find is different between first recod of this group (-3) and the rest of records.

Any help would be greatly appreciated. Thank you

Hi @bbui226

Quite a few ways to go about this. One could be through a Column Expression and use column("column1") - column("column1", - rowIndex())

Make sure that the Access window is set as per the Advanced section.

image

5 Likes

Hi @ArjenEX,
Thank you for the solution.


It not given me the correct answer. I wondered is it because the Row ID here?
2nd, for my better understading, can you explain how (colum - rowindex()) works?

1 Like

Hi @bbui226

The RowID is not of influence here. I suspect you’re applying the function incorrectly since it seems to do Sum(Fcst_Qty) + RowIndex instead of the acces function.

column("column1", - rowIndex())

This takes the value of column1 but with an offset based on the second parameter. -1 means the previous row, +1 means the next row, etc.

The rowIndex gives an indication of the offset of the current row against the first row, which is your desired logic. So pratically it does the value of the current row minus the value of the -1,-,2,-3 or -4 row.

4 Likes

Thank you :+1:
I get the correct result now

2 Likes

Hi @bbui226,

As @ArjenEX mentioned there are a number of ways to do this, and here is an example of a non-scripting method (although it will use a few more nodes!)

calculating group difference from row 1.knwf (87.7 KB)


Here, within the group loop, it splits off row one from the other rows in the group, and then cross joins (appends columns for that row) back onto the other rows.

The Math Formula node calculates the difference for each row using the appended value column from row 1, and then the first row is concatenated back onto the top of the remaining rows for the group to reassemble the full table.

3 Likes