sum columns excluding collums with the same header as the value in a different columns

[sands]

Hey All,

I would like to sum the values of second column and futher on but without the cell in the collumn with the same collumn header as the first cell in that particular row. So in th picture below i want to sum row 2024 without the cell in column 2024 and sum row 2025 without column 2025 and so on.

Does anyone have ideas how to do this? Thanks in advance for your reactions!

[sands]

I now tried to use this code below in the column expression node but it gives me the sum including the column i want excluded:

// Initialize variables
var sum = 0;
var firstColumnHeader = column(“jaar 20240101”);

// Get all column names
var allColumnNames = columnNames();

// Iterate through all columns starting from the second column
for (var i = 1; i < allColumnNames.length; i++) {
var currentColumnName = allColumnNames[i];

// Check if the current column name is different from the firstColumnHeader
if (currentColumnName !== firstColumnHeader) {
    // Add the value of the current column to the sum
    sum += column(currentColumnName);
}

}

// Return the final sum
sum

[gonhaddock]

Hello @sands
I am just guessing but…
You can unpivot to a single column the whole data and keep it aside. Then you can do a column aggregation for all the columns.
You can join the unpivoted data afterwards, and substract the joined column.

BR

PS.- If you share some sample data, we can arrange a workflow for you.

[sands]

Thanks gonhaddock!

The unpivot helped a lot. After the unpivot i filtered the rows with the same years so i got a table with only 1 row per year and with the right value to substract in the connection aftwerwards.

Thanks again!