community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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
SO0001NQ180,000.00 20.0021.009.0000 (6.630000)150,000.0021.000 150,000.00150,000.00
SO0005NQ160,000.00 20.0021.009.0000 (6.630000)160,000.0021.000 160,000.00160,000.00
SO0009NQ140,000.00 20.0021.009.0000 (6.630000)170,000.0021.000 170,000.00170,000.00
SO0013NQ120,000.00 20.0021.009.0000 (6.630000)180,000.0021.000 180,000.00180,000.00
SO0017NQ100,000.00 20.0021.009.0000 (6.630000)190,000.0021.000 190,000.00190,000.00
SO0021NQ80,000.00 20.0021.009.0000 (6.630000)200,000.0021.000 200,000.00200,000.00
SO0025NQ60,000.00 20.0021.009.0000 (9.500000)210,000.0021.000 210,000.00210,000.00
SO0029NQ40,000.00 20.0021.009.0000 (9.500000)220,000.0021.000 220,000.00220,000.00
SO0033NQ20,000.00 20.0021.009.0000 (9.500000)230,000.0021.000 230,000.00230,000.00
  _________________      __________________  ________________________________
 ANTI-DILUTIVE
OPTIONS
900,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.00
SO0003NQ275,000.00 20.0021.009.0000 (7.600000)4,900.0021.000 1,200.0060.00
SO0004NQ270,000.00 20.0021.009.0000 (7.600000)4,800.0021.000 1,300.0070.00
SO0006NQ265,000.00 20.0021.009.0000 (7.600000)4,700.0021.000 1,100.0040.00
SO0007NQ260,000.00 20.0021.009.0000 (7.600000)4,600.0021.000 1,200.0050.00
SO0008NQ255,000.00 20.0021.009.0000 (7.600000)4,500.0021.000 1,300.0060.00
SO0010NQ250,000.00 20.0021.009.0000 (7.600000)4,400.0021.000 1,100.0070.00
SO0011NQ245,000.00 20.0021.009.0000 (7.600000)4,300.0021.000 1,200.0040.00
SO0012NQ240,000.00 20.0021.009.0000 (7.600000)4,200.0021.000 1,300.0050.00
SO0014NQ235,000.00 20.0021.009.0000 (7.600000)4,100.0021.000 1,100.0060.00
SO0015NQ230,000.00 20.0021.009.0000 (7.600000)4,000.0021.000 1,200.0070.00
SO0016NQ225,000.00 20.0021.009.0000 (7.600000)3,900.0021.000 1,300.0040.00
SO0018NQ220,000.00 20.0021.009.0000 (7.600000)3,800.0021.000 1,100.0050.00
SO0019NQ215,000.00 20.0021.009.0000 (7.600000)3,700.0021.000 1,200.0060.00
SO0020NQ210,000.00 20.0021.009.0000 (7.600000)3,600.0021.000 1,300.0070.00
SO0022NQ205,000.00 20.0021.009.0000 (7.600000)3,500.0021.000 1,100.0040.00
SO0023NQ200,000.00 20.0021.009.0000 (7.600000)3,400.0021.000 1,200.0050.00
SO0024NQ195,000.00 20.0021.009.0000 (7.600000)3,300.0021.000 1,300.0060.00
SO0026NQ190,000.00 20.0021.009.0000 (7.600000)3,200.0021.000 1,100.0070.00
SO0027NQ185,000.00 20.0021.009.0000 (7.600000)3,100.0021.000 1,200.0040.00
SO0028NQ180,000.00 20.0021.009.0000 (7.600000)3,000.0021.000 1,300.0050.00
SO0030NQ175,000.00 20.0021.009.0000 (7.600000)2,900.0021.000 1,100.0060.00
SO0031NQ170,000.00 20.0021.009.0000 (7.600000)2,800.0021.000 1,200.0070.00
SO0032NQ165,000.00 20.0021.009.0000 (7.600000)2,700.0021.000 1,300.0040.00
  _________________      __________________  ________________________________
 DILUTIVE
OPTIONS
5,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

 

Highlighted
Alteryx
Alteryx

Hi @easher003 

 

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

Alteryx Partner

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

Labels