Grand Total and Sub Totals for Data

[Inactive User]

How can I total and subtotal the following data set? I am looking to subtotal the first set, subtotal the second set and then grand total the two sets, essentially what is already in there but I would like to be able to run it through alteryx as the report I get is all hardcoded. 

Option Number	Type	Weighted Shares Outstanding		Average Market Value ($)	Price ($)	Intrinsic Value/ Fair Value Expense ($)		Gain per Share ($)	Gain ($)	Tax Rate(%)		Tax Deduction ($)	Tax Benefit Shares
SO0001	NQ	180,000.00		20.00	21.00	9.0000		(6.630000)	150,000.00	21.000		150,000.00	150,000.00
SO0005	NQ	160,000.00		20.00	21.00	9.0000		(6.630000)	160,000.00	21.000		160,000.00	160,000.00
SO0009	NQ	140,000.00		20.00	21.00	9.0000		(6.630000)	170,000.00	21.000		170,000.00	170,000.00
SO0013	NQ	120,000.00		20.00	21.00	9.0000		(6.630000)	180,000.00	21.000		180,000.00	180,000.00
SO0017	NQ	100,000.00		20.00	21.00	9.0000		(6.630000)	190,000.00	21.000		190,000.00	190,000.00
SO0021	NQ	80,000.00		20.00	21.00	9.0000		(6.630000)	200,000.00	21.000		200,000.00	200,000.00
SO0025	NQ	60,000.00		20.00	21.00	9.0000		(9.500000)	210,000.00	21.000		210,000.00	210,000.00
SO0029	NQ	40,000.00		20.00	21.00	9.0000		(9.500000)	220,000.00	21.000		220,000.00	220,000.00
SO0033	NQ	20,000.00		20.00	21.00	9.0000		(9.500000)	230,000.00	21.000		230,000.00	230,000.00
		_________________							__________________			__________________	______________
	ANTI-DILUTIVE OPTIONS	900,000.00							1,710,000.00			1,710,000.00	1,710,000.00
SO0002	NQ	280,000.00		20.00	21.00	9.0000		(7.600000)	5,000.00	21.000		1,100.00	50.00
SO0003	NQ	275,000.00		20.00	21.00	9.0000		(7.600000)	4,900.00	21.000		1,200.00	60.00
SO0004	NQ	270,000.00		20.00	21.00	9.0000		(7.600000)	4,800.00	21.000		1,300.00	70.00
SO0006	NQ	265,000.00		20.00	21.00	9.0000		(7.600000)	4,700.00	21.000		1,100.00	40.00
SO0007	NQ	260,000.00		20.00	21.00	9.0000		(7.600000)	4,600.00	21.000		1,200.00	50.00
SO0008	NQ	255,000.00		20.00	21.00	9.0000		(7.600000)	4,500.00	21.000		1,300.00	60.00
SO0010	NQ	250,000.00		20.00	21.00	9.0000		(7.600000)	4,400.00	21.000		1,100.00	70.00
SO0011	NQ	245,000.00		20.00	21.00	9.0000		(7.600000)	4,300.00	21.000		1,200.00	40.00
SO0012	NQ	240,000.00		20.00	21.00	9.0000		(7.600000)	4,200.00	21.000		1,300.00	50.00
SO0014	NQ	235,000.00		20.00	21.00	9.0000		(7.600000)	4,100.00	21.000		1,100.00	60.00
SO0015	NQ	230,000.00		20.00	21.00	9.0000		(7.600000)	4,000.00	21.000		1,200.00	70.00
SO0016	NQ	225,000.00		20.00	21.00	9.0000		(7.600000)	3,900.00	21.000		1,300.00	40.00
SO0018	NQ	220,000.00		20.00	21.00	9.0000		(7.600000)	3,800.00	21.000		1,100.00	50.00
SO0019	NQ	215,000.00		20.00	21.00	9.0000		(7.600000)	3,700.00	21.000		1,200.00	60.00
SO0020	NQ	210,000.00		20.00	21.00	9.0000		(7.600000)	3,600.00	21.000		1,300.00	70.00
SO0022	NQ	205,000.00		20.00	21.00	9.0000		(7.600000)	3,500.00	21.000		1,100.00	40.00
SO0023	NQ	200,000.00		20.00	21.00	9.0000		(7.600000)	3,400.00	21.000		1,200.00	50.00
SO0024	NQ	195,000.00		20.00	21.00	9.0000		(7.600000)	3,300.00	21.000		1,300.00	60.00
SO0026	NQ	190,000.00		20.00	21.00	9.0000		(7.600000)	3,200.00	21.000		1,100.00	70.00
SO0027	NQ	185,000.00		20.00	21.00	9.0000		(7.600000)	3,100.00	21.000		1,200.00	40.00
SO0028	NQ	180,000.00		20.00	21.00	9.0000		(7.600000)	3,000.00	21.000		1,300.00	50.00
SO0030	NQ	175,000.00		20.00	21.00	9.0000		(7.600000)	2,900.00	21.000		1,100.00	60.00
SO0031	NQ	170,000.00		20.00	21.00	9.0000		(7.600000)	2,800.00	21.000		1,200.00	70.00
SO0032	NQ	165,000.00		20.00	21.00	9.0000		(7.600000)	2,700.00	21.000		1,300.00	40.00
		_________________							__________________			__________________	______________
	DILUTIVE OPTIONS	5,340,000.00							92,400.00			28,800.00	1,320.00
		_________________							__________________			__________________	______________
TOTALS		6,240,000.00							1,802,400.00			1,738,800.00	1,711,320.00

[wdavis · Accepted solution]

Hi @Inactive User 

To do this, you will need to separate your input data into 3 streams to perform each of the summations you require before bringing the data back together.

The first stream will be your data in its current format with the total fields removed, you can then use the summarize tool to get a total sum.

For the other 2 streams, you will need to split your input data. The best way to do this will be to use the Select Records tool (you will require one for each stream) - Select the specific rows you require for each table. Then, as above, if you add a summarize tool to get the sub totals for both.

Finally, you will need to use the Union tool to bring all of your data back together. You will need to give the fields from your summarize tool a similar name, so that you can join these by name within the Union tool.

I hope this helps, but if you get stuck using any of these tools then there is more information available in the examples within Designer. Let me know how you get on.

Regards

Will

[john_miller9]

I prepared a solution that will dynamically account for however many 'Categories' you have (e.g. Dilutive Options, Anti-Dilutive, etc.).  See if this meets your needs...

[ypt]

Hi guys!

Any ideas on how can I make the Grand Total stay as the last row in my dataset after the Union? For instance, in this case if there are letters that come after G in the Category column then ascending sort on Category will not work. 

Thanks in advance

[wdavis]

Hi @ypt,

You can add a sorting column into your dataset to help with this. If for instance you used a RecordID field to create your initial order. Then you could use a Formula tool to build an IF statement along the lines of IF [Column] = "Grand Total" THEN 1000 ELSE [RecordID] ENDIF

Then you can re-sort the data based on this field to keep Grand Total at the bottom (you may need to put the number higher than 1,000 though!). Prior to output you can remove this field as it won't be required for the output.

Thanks,

Will