How to create a dinamic sum of columns "TOTAL" like PIVOT excel

[Felipereis50]

Hi friends,

Maybe this is a simple question.

I’ve set up a visualization with a temporal structure, where Year and Month are displayed horizontally.
I used UNPIVOT and PIVOT to achieve the result below.

What I would like to do is create a “TOTAL” column that will be the sum of the periods that can be displayed. Similar to Excel

The challenge is that the Year and Month columns are dynamic and can vary depending on the user’s period selection.
I could have periods from 202301 to 202312, or interspersed periods like 202301 and 202308, for example.

The important thing is that I have a TOTAL column with the sum of all existing periods.

I could manually sum using a Math node or Column Expressions, but my formula would become hard-coded and the column names would vary, causing errors.

How can I create this sum column?

I’m thinking that at some point before the PIVOT or UNPIVOT, I would have to create the sum column so that when I do the PIVOT, it would be naturally created. But I haven’t been able to figure it out.

Below is a simple model for anyone who can help me.

TOTAL COLUMN.knwf (20.2 KB)

I’m using table view and would be nice to have a feature inside (configuration of the node ) to sum by column or rows

[takbb]

Hi @Felipereis50 , I can see that unpivot/pivot or similar might be an option, but given your amount columns have standardised names (yyyymm), could you not use Column Aggregator?

Select columns using regex pattern, for example:
20[0-9]{4}

And create TOTAL column as the sum of the selected columns

[Felipereis50]

@takbb

20[0-9]{4}

That’s exactly what I need.
Easier to do than I imagine.

(one more node learned)

Thanks Brian