Sum based on certain(not all) rows

Hi everyone,

Quick question. What is the best solution to sum certain a rows together and also display the sum in rows. For e.g.
Businesses Jan Feb Mar
Mercedes. $2. $1. $2
Ford $3. $2. $1
Audi $1. $3. $3
Chevrolet. $4. $4. $4

Step 2:
Total American cars $7. $6. $5
Total German cars. $3. $4. $5

Using the info in the table above step two I want to generate step 2 information and have both data presented exactly like above.

What is the best way to go about doing that. Just to be clear the table information is already formatted in that way. I just need the total per row and then to have it vertically aligned with the table information.
I tried using transpose and that didn’t work unfortunately.

1 Like

Hi,
You will need to add an extra column to your original table that specifies whether it is an American or German brand (e.g. with Rule Engine). Then you can use GroupBy to group on that column and sum on the months to get your result table.
Kind regards
Alexander

3 Likes

Thank you so much. It worked. And I’ll concatenate. Just that I have a few more groups to do and then I’ll always sum based on a certain criteria in a group so I was looking for a node that could do that quickly so I don’t have to join or concatenate so much. But thank you!

1 Like

Hi,
If you concatenate the result table and the original table, everything will be there only once. But first you should make sure that the column names are matching. You can do that using a Column Rename node.
Kind regards
Alexander

2 Likes

Thanks I did that and it worked. So that I you very much! But one thing is missing the value “Total American cars” and “Total German cars”. When I concatenate it’s empty. How do i input a value there? Do I have to use the missing value node? Or is there an easy or better way. Because I have to then add the Total German cars + other European cars to then get “Total European cars” and display them in the same way above. Thank you so much and I look forward to your reply!

Hi,
If you want it to be in the same column as the manufacturer names, you have to give the column with the aggregation labels the same name before concatenating.
Kind regards
Alexander

2 Likes

Thanks for getting back to me. I did that and that’s why I have a missing value in that column. E.g.

Business
Mercedes
Audi
Ford
Chevrolet
————(I wanted to know how I can put Total here. I calculated the total using Groupby and only calculated the total for all businesses across the months)
American cars
European cars

I just now realize that’s a different question regarding having a column where I can put a name in the column next to the aggregated column when I aggregate and not group.

That’s the final step. The only solution i see is to add a column with the same name as “Businesses” to get the Title “Total Businesses” in the same row as the aggregated sum pro month.

Thank you! Hope it makes sense.

Thank you. I found what I wanted with the Rule engine. Thanks again!

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