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 TypeWeighted Shares Outstanding Average Market Value ($) Price ($)Intrinsic Value/ Fair Value Expense ($) Gain per Share ($) Gain ($) Tax Rate(%) Tax Deduction ($) Tax Benefit SharesSO0001NQ180,000.00 20.0021.009.0000 (6.630000)150,000.0021.000 150,000.00150,000.00SO0005NQ160,000.00 20.0021.009.0000 (6.630000)160,000.0021.000 160,000.00160,000.00SO0009NQ140,000.00 20.0021.009.0000 (6.630000)170,000.0021.000 170,000.00170,000.00SO0013NQ120,000.00 20.0021.009.0000 (6.630000)180,000.0021.000 180,000.00180,000.00SO0017NQ100,000.00 20.0021.009.0000 (6.630000)190,000.0021.000 190,000.00190,000.00SO0021NQ80,000.00 20.0021.009.0000 (6.630000)200,000.0021.000 200,000.00200,000.00SO0025NQ60,000.00 20.0021.009.0000 (9.500000)210,000.0021.000 210,000.00210,000.00SO0029NQ40,000.00 20.0021.009.0000 (9.500000)220,000.0021.000 220,000.00220,000.00SO0033NQ20,000.00 20.0021.009.0000 (9.500000)230,000.0021.000 230,000.00230,000.00 _________________ __________________ ________________________________ ANTI-DILUTIVE OPTIONS900,000.00 1,710,000.00 1,710,000.001,710,000.00 SO0002NQ280,000.00 20.0021.009.0000 (7.600000)5,000.0021.000 1,100.0050.00SO0003NQ275,000.00 20.0021.009.0000 (7.600000)4,900.0021.000 1,200.0060.00SO0004NQ270,000.00 20.0021.009.0000 (7.600000)4,800.0021.000 1,300.0070.00SO0006NQ265,000.00 20.0021.009.0000 (7.600000)4,700.0021.000 1,100.0040.00SO0007NQ260,000.00 20.0021.009.0000 (7.600000)4,600.0021.000 1,200.0050.00SO0008NQ255,000.00 20.0021.009.0000 (7.600000)4,500.0021.000 1,300.0060.00SO0010NQ250,000.00 20.0021.009.0000 (7.600000)4,400.0021.000 1,100.0070.00SO0011NQ245,000.00 20.0021.009.0000 (7.600000)4,300.0021.000 1,200.0040.00SO0012NQ240,000.00 20.0021.009.0000 (7.600000)4,200.0021.000 1,300.0050.00SO0014NQ235,000.00 20.0021.009.0000 (7.600000)4,100.0021.000 1,100.0060.00SO0015NQ230,000.00 20.0021.009.0000 (7.600000)4,000.0021.000 1,200.0070.00SO0016NQ225,000.00 20.0021.009.0000 (7.600000)3,900.0021.000 1,300.0040.00SO0018NQ220,000.00 20.0021.009.0000 (7.600000)3,800.0021.000 1,100.0050.00SO0019NQ215,000.00 20.0021.009.0000 (7.600000)3,700.0021.000 1,200.0060.00SO0020NQ210,000.00 20.0021.009.0000 (7.600000)3,600.0021.000 1,300.0070.00SO0022NQ205,000.00 20.0021.009.0000 (7.600000)3,500.0021.000 1,100.0040.00SO0023NQ200,000.00 20.0021.009.0000 (7.600000)3,400.0021.000 1,200.0050.00SO0024NQ195,000.00 20.0021.009.0000 (7.600000)3,300.0021.000 1,300.0060.00SO0026NQ190,000.00 20.0021.009.0000 (7.600000)3,200.0021.000 1,100.0070.00SO0027NQ185,000.00 20.0021.009.0000 (7.600000)3,100.0021.000 1,200.0040.00SO0028NQ180,000.00 20.0021.009.0000 (7.600000)3,000.0021.000 1,300.0050.00SO0030NQ175,000.00 20.0021.009.0000 (7.600000)2,900.0021.000 1,100.0060.00SO0031NQ170,000.00 20.0021.009.0000 (7.600000)2,800.0021.000 1,200.0070.00SO0032NQ165,000.00 20.0021.009.0000 (7.600000)2,700.0021.000 1,300.0040.00 _________________ __________________ ________________________________ DILUTIVE OPTIONS5,340,000.00 92,400.00 28,800.001,320.00 _________________ __________________ ________________________________ TOTALS 6,240,000.00 1,802,400.00 1,738,800.001,711,320.00 Sample Data.xlsx

Grand Total and Sub Totals for Data

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

Sample Data.xlsx

Workflow

Datasets

Help

Accepted answers

wdavis

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

All comments

wdavis

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.

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...

Grand Total and Sub Totals for Data.png

Grand Total and Sub Totals for Data.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703