# Sum by particular columns

I have a table with sales in different country, from Jan 2021 to Dec 2022, for example, I want to sum the sales figure by quarter, how to do it in KNIME?

There are a few ways, depending on your specific goals, but Iâ€™d go with the Math Formula node or the Column Aggregator node or the Column Expressions node. For the first 2 options, youâ€™ll need to use a different node for each calculation. The Column Expressions node would allow you to use a single node for all the calculations.

1 Like

That means I need to hard code in the formula that 2021-01-01, 2021-02-01, 2021-03-01 as 2021Q1 and so onâ€¦? I try to avoid this as the monthly data maybe updated or I may need to sum up the fiscal year which is from April 21 to Mar 22, etc.

Hi @anguslou,

Find below a possible solution. I made this by using a table with the quarters.

Itâ€™s easier to maintain this table (in my opinion), which is not, or less, the case for a hardcoded solution.

Hope it helps.
Br,
Samir

3 Likes

Hi @anguslou ,

I took a similar approach to @SamirAbida, as for this type of job, unpivoting is often a good first step, as it allows you to put your tabulated data into â€śrowsâ€ť which are much better to work with.

The derivation of â€śquarter namesâ€ť can be done with String Manipulation which allows some mathematical operations, and I noticed that your Quarters appear to be based on Calendar years rather than Financial years, but I created two different String Manipulation nodes to derive each. You can manually adjust the workflow and join which of these is appropriate to the subsequent GroupBy node.

I added a couple of extra columns for testing purposes.
Using â€śFinancial yearsâ€ť the output is as follows:

and as â€śCalendar yearsâ€ťâ€¦

The String Manipulation calculation for â€śFinancial Yearsâ€ť takes a little explaining:

``````string(
toInt(substr(\$ColumnNames\$,5,2))/4==0
?join(string(toInt(substr(\$ColumnNames\$,0,4))-1),"Q4")
:join(substr(\$ColumnNames\$,0,4),"Q", string(toInt(substr(\$ColumnNames\$,5,2))/4))
)
``````

What this says is if the integer value of the two characters in the â€śdateâ€ť starting at position 5 (counting from 0), when divided by 4 equals 0, then we want to subtract 1 from the year portion, and then append â€śQ4â€ť. Otherwise, use the year portion, and append Q followed by the int value of the month part divided by 4.

The â€śCalendar yearâ€ť version of the calculation is similar but simpler, as it doesnâ€™t require the conditional part.

``````join(substr(\$ColumnNames\$,0,4),"Q",string(toInt(substr(\$ColumnNames\$,5,2))/4+1))`
``````

It uses the year, as supplied and divides the month by 4 as an integer and then adds 1 to it. So 01,02,03 become 1; 04, 05, 06 become 2 and so onâ€¦

6 Likes

Hi takbb, thank you. This is exactly what I want. I also learn how you are using â€ś?â€ť and â€ś:â€ť in String Manipulation, that is very useful.

3 Likes

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