Subtotals

[nausherc]

Hi Experts, I have been struggling to use subtotals in my output. Is there a (simple) way to include subtotals in the output? Below is a sample data.

Class	Section	Students
I	A	100
I	B	150
II	A	200
II	B	250
II	C	300

I would like to have subtotals for each class like the ones we see in pivot tables. I tried the CReW macros(Add Totals), but it totals the full range and not by each group. My workflow goes - Many tools to Union-Summarise-Table-Layout-Render.

Thanks!

[vivaisun11]

Hello.

Thanks for the suggestion that you provided.

I was able to follow until the Sort tool, but in my solution, when I pass this data into the Table tool, how do I still keep the same order when some columns are not part of the grouped data in the Table tool?

Also, how do I remove 'Null' from the columns that don't have any data?

[Kenda]

Hi @vivaisun11 

What are all of the fields you're grouping by in the Table tool? If you just group on the SCAC field, it looks like you could get the output you're looking for.

[vivaisun11]

Hi @Kenda

Thanks for getting back so quick.

I am grouping it by SCAC, Email and Subject since I need to use the Email and Subject fields in the Email Tool from upstream inputs.

However, since these cells show 'Null' for the Subtotal row, the table is being split into two and not showing as a consolidated view.

[Kenda]

Hi @vivaisun11 

Without seeing your full dataset, I just have to guess, but it sounds like you have two options. You could fill in the values that are currently null for email and subject with the same value as the prior rows so that they are all grouped together. Another option could be to put this piece into a batch macro.

[vivaisun11]

Hi @Kenda 

My bad in not attaching a sample workflow.

Attached it here :)

Will also try the suggestions that you made.

Thanks for taking a look!

[Kenda]

Hi @vivaisun11 

Thanks for the workflow. I tested adding two Multi-Row Formula tools, and I believe I was able to achieve what you're looking for. 

Here is an example for the EMAIL field:

Do the same thing for your SUBJECT field. Put these after your Sort tool but before your Table tool.

Hope this helps!

[vivaisun11]

Hi @Kenda 

That worked like a charm! Thanks so much for helping me with this!