How to substract after pivoting

Hello, I used Pivoting node to create a pivot table with data for users for 2018 and 2019 years, I need to create a new column with subtraction between 2019 and 2018 data. When I use Math Formula node it works not with pivot table from the Pivoting node but with initial data (or I don’t know how to apply this node correctly).

1 Like

could you show your example data and result that you want?

image
@Mohammad_Nazemi, I need to get “Difference” column.
I would also appreciate pieces of advice on how to calculate Grand Total for each column in pivot table and how to format pivot table when writing Excel file. Thank you in advance!

@Skirpichnikova_Ksenia
test.knwf (26.9 KB)

This is not the best but it’s first answer I find.
I hope to be useful.

1 Like

Wow, thanks for the job done!

@Mohammad_Nazemi, you work with a table that is why you have columns 2019 and 2018.
I had a Pivoting node before and when I use Math Formula node after Pivoting node columns 2018 and 2019 are not available for me to use them in expression field. Only column “Total CHF” which was used as Manual aggregation in Pivoting node is available

pay attention to the column type. you can use numerical value. and your target column maybe is string.

1 Like

Hello there!

@Mohammad_Nazemi you can also use Math Formula (Multi Column) to get column sum for multiple columns using only one node. Check modified example:
test_ipazin.knwf (65.0 KB)

@Skirpichnikova_Ksenia if your columns are string they will not show up in Math Formula node so you need to use String To Number and convert them to numeric type. To format Excel files check out Continental Nodes for KNIME extension. Here you can ind more info about it and links to examples: https://www.knime.com/community/continental-nodes-for-knime

Br,
Ivan

1 Like

@ipazin exactly, thanks.

1 Like

@Mohammad_Nazemi, @ipazin, thanks for your time and input! Column was not string but I already solved the problem I had

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.