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!

[Kenda]

Hey @nausherc!

Have you tried the Running Total tool and grouping by your Class field?

[nausherc]

Hi BarnesK, 

Yes, I tried that as well. But the totals are continuous and calculated at columns, while I am looking to use rows.

[Kenda]

@nausherc Can you post an example of what you want your sample output to look like, please?

[Kenda]

Hey @nausherc 

Check out this workflow to see if it is what you're looking for.

[nausherc]

This is how the output should look.

While, I found a workaround of Union-ing 2 different summaries (one with all fields and the other with only the grouping field) and then ->sort -table-layout render, I feel its not efficient.

[Kenda · Accepted solution]

@nausherc The first workflow I attached did not have a grand total row, so I have attached an updated workflow that contains all the subtotals and the grand total. 

Is this what you're looking for?

[nausherc]

Hi BarnesK, Yes, both the workflow you provided is close to what I am trying to achieve. I 'll try it in my workflow and report back later today. Thanks for the help!

[nausherc]

That worked, Thanks :)

[ARAVIND1]

Hey,
I am finding trouble in comparing total, Main account will have Account ID and balance, however sub account will have just the totals, please suggest a solution to compare the numbers, 

the catch is sub account will not have account ID at the total, rather it will have at the start (see 9 and 16). Similarly, subtotal will run to multiple batches

for example,

Row	Type	Account	Details	T4-2018	Query
1	S	100000000	Total Assets
2	D	110000000	Liabilities	10075267.67	Please compare the amount to total in Row 14
3	D	130000000	Intangibles	0	Please compare the amount to total in Row 20
4	D	150000000	Assets	0
5	D	170000000	Engagement	0
6	D	190000000	Other Assets	195876.01
7	T			10271143.68
8	Spacer
9	S	110000000	Liabilities
10	D	112000000	Cash	0
11	D	114000000	Deposits	9651525.11
12	D	115000000	Suspense	0
13	D	116000000	Cheque	423742.56
14	T			10075267.67
15	Spacer
16	S	130000000	VALEUR MOBILIERE
17	D	132000000	EMISE OU GARANTIE PAR CANADA	0
18	D	135000000	EMISE OU GAR.-PROV. ET MUN.	0
19	D	137000000	AUTRE VALEUR MOBILIERE	0
20	T			0