Adding monthly total at end of table

I have a table that I would like to add some values at the bottom.

Here is an example of the original table:

Date Product A Product B Product C
20181125 30,000 13,000 5,000
20181202 2,000 0 4,000
20181203 23,000 17,000 19,000
20190105 17,000 8,000 0

Here is what I would like it to look like:

Date Product A Product B Product C
20181125 30,000 13,000 5,000
20181202 2,000 0 4,000
20181203 23,000 17,000 19,000
20190105 17,000 8,000 0
Nov 30,000 13,000 5,000
Dec 25,000 17,000 23,000
Jan 17,000 8,000 0
Total 144,000 68,000 56,000

Does anybody know how this could be done?

Hi,

You can use the “GroupBy” node to calculate the sum for each column (leave the group column empty and aggregate those product columns by sum function).

Then use the “Concatenate” node to add the row created in previous step to the original dataset.

At the end you can use the “Missing Value” node to change the value of the date cell in that new row to “Total” for example.

sum%20of%20each%20column

sum each column.knwf (19.0 KB)

Best,
Armin

2 Likes

Thank you very much for your reply Armin :slight_smile:

I have the date in this format “20181125” and not “Dec”, so I’m not sure if it will work, but I’ll give it a go, and let you know if it works.

I can add additional rows to the bottom of the table which is great, but it just duplicates the same table. For example:

Date Product A Product B Product C
20181125 30,000 13,000 5,000
20181202 2,000 0 4,000
20181203 23,000 17,000 19,000
20190105 17,000 8,000 0
20181125 30,000 13,000 5,000
20181202 2,000 0 4,000
20181203 23,000 17,000 19,000
20190105 17,000 8,000 0

I think I need another node to convert this “20190105” to this “JAN”.

I’ve sort of got it to work with the above flow. It’s a really messy way of doing it though, and I have to use an excel file to convert the month number (4) to the name (Apr).

I’m sure that there is a better way of doing it.

I managed to get the table to look like this now:

Date Product A Product B Product C
20181125 30,000 13,000 5,000
20181202 2,000 0 4,000
20181203 23,000 17,000 19,000
20190105 17,000 8,000 0
20181125 30,000 13,000 5,000
NovTotal 2,000 0 4,000
Dec Total 2,000 0 4,000
Jan Total 23,000 17,000 19,000

⇑ The correct values are added up from each much, the above is just an example.

Now my last task is to get a Grand Total at the bottom of the table :slight_smile:

Date Product A Product B Product C
20181125 30,000 13,000 5,000
20181202 2,000 0 4,000
20181203 23,000 17,000 19,000
20190105 17,000 8,000 0
20181125 30,000 13,000 5,000
NovTotal 2,000 0 4,000
Dec Total 2,000 0 4,000
Jan Total 23,000 17,000 19,000
Grand Total 23,000 17,000 19,000

So you want to add both monthly total and overall total.

I have created a new version of the workflow in which I’ve created a dictionary (using Table Creator) which contains 12 rows and 2 columns. First column month number (01, 02, …) and 2nd column month name (January, …).

Then used String Manipulation to create rules from month number and another String Manipulation to extract the month number from the complete date format in the original dataset. Then I used a Rule Engine (Dictionary) to convert dates to month name. And after that I used one GroupBy node to calculate total for each month and another one to calculate the overall total. The rest is almost the same as what I’ve done before.

sum each column.knwf (42.7 KB)

And this is the final output:

output

Best,
Armin

4 Likes

You’re example works perfectly, and it’s exactly what I’m after.

At the moment I cannot get it to work in my workflow, but I’m going to keep trying. I’ll let you know if I can get it to work.

I think Note 11 is the problem one (for me), it gives the following error message “Please select at least one group or aggregation column”.

I have used the aggregation function on column type not on each column separately.
In the configuration window for the GroupBy node go to the “Type Based Aggregation” tab and you will see the setting I’ve configured.

Best,
Armin

1 Like

I have used the aggregation function on column type not on each column separately.
In the configuration window for the GroupBy node go to the “Type Based Aggregation” tab and you will see the setting I’ve configured.
Thank you for your suggestion Armin, you’ve been so helpful.
I noticed that it wasn’t the “Type Based Aggregation”, it was the fact that my Product A, B and C fields were set as ‘D’, where the workflow that you created was set to ‘I’.

I used the “Double to Int” Node after the Rule Engine, to convert ‘D’ to ‘I’ and it worked! Thank you very much.

Here’s what the flow looks like now:

One question, Node 99 has an exclamation mark. It gives the following error:

“Column Product A” still contains missing values.
“Column Product B” still contains missing values,

I think this is because I don’t always have values for each product.

Apart from that, I’m so greatful for you sharing your wisdom.

To convert missing values in product columns to zero: In the configuration window of the Missing Value node, under “Default” tab, use a fixed value(for example 0, or any other option as you wish) for your numeric columns.

Best,
Armin

2 Likes

That’s great, thank you very much. I don’t get the error message anymore! :smiley:

2 Likes