Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Grand Total and Sub Totals for Data

Inactive User
Not applicable

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

 

4 REPLIES 4
wdavis
Alteryx
Alteryx

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
11 - Bolide

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

ypt
7 - Meteor

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

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

Labels