# Pivot table: calculate "% of Column Total" using variable

Hi all,

New to KNIME, I make very slow progress but learn a lot. My main challenges is that I am used to Excel to visualise/manipulate data, KNIME (albeit very visual) is very different! (Also, I find it annoying that I can’t see 2 Node Configure windows at the same time; or 2 Projects at the same time. That would help a lot

I am trying to calculate what would simply be “% of Column Total” in an Excel pivot table.
From this topic, I tried using variable to perform this calculation but had no luck as using either Table Row to Variable or Column Row to Variable meant my % were based on either the 1st Column or the 1st Row, not the current column.

I found a solution using a Joiner, but curiosity got the best of me and I’d like to know if there is an alternative solution using a Variable.
In short: can anyone fix the flow at the top ?

Thanks !
Pivot as percentage.knwf (93.3 KB)

Hi @Jerome_G,

welcome to Knime and feel rest assured that pretty much everyone had it’s first data-encounter with Excel

For a beginner your workflow exhibits a really good and professional approach. So you can proudly say to yourself you made the right choice with Knime.

About your question, I happen to work with the Stacked Area recently and know your struggle. The option to convert to a percentage chart is missing in the new nodes. Though, you might use the JavaScript based simplifying the process. But, you loose control whenever you want to calc the average or median.

Knime Devs stated to have followed the pareto principle when integrating the new ECharts. I raised a feature request so maybe you vote for it to get integrated

About your question using a variable, yes there is always another option. Here is mine:

• Calc totals via Pivot (note the 2nd and 3rd port provide totals)
• Loop per column or in my case row (depending on your data structure)
• Filter for column or row
• Do some math and you are done

Best
Mike

3 Likes

Hi @mwiegand,

Thanks for the kind words!
Kudos to the KNIME training and community, I have found most of my knowledge from those sources - and still so much to learn!

I checked out your Stacked Area Chart topic and we have the same requirement, you got my vote! I worked around it calculating the % but it adds nodes and possibly computing time, it may be more efficient if native from the node.
Your topic gave me new ideas which I’m looking forward to experiment with.

Thanks also for the solution using variable. I start to understand how this works and for this specific case I find it adds unnecessary complexity having the start/end of the loop. There are probably cases where this is more relevant, but since I have my data in the pivot I think I’m better of joining the data and doing the math there.

ps: one last question: how do you display the grid behind your nodes ? I would love this to align my nodes, not OCD but quite

Hi @Jerome_G,

you are most welcome!

the grid only is visible using thee Classic UI which, at this point is still offering some major advantages over the Modern UI like the grid, snap to grid, node alignment etc…

Click menu in the top right and select the last option:

Then click the little wrench icon:

Which will open the config dialogue:

The same setting you will find under preferences to manage the setting application wide:

Worth to note that for already existing workflows the setting must be applied via the first method.

About one Node vs. splitting the effort between several. My experience is that you should try stick with the visual, more granular approach as this is the core strength of Knime. Only on rare occasions coding everything in one node is superior.

Reasons for that are numerous but you can imagine it as one specialized and therefore optimized node vs. code the that required libraries to get loaded first, no streaming possible and defying the true benefit of Knime.

Best
Mike

1 Like

Thank you Mike, most helpful as always!

1 Like

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