Designer| Sub-Total Line (dynamic)

Hi All,

I have a dataset which need to be sub-totaled by col E - "Firm" for the value in column H -"value". The resulting output attached in same file - "Output" tab.

I also need all the sub-total lines together in one place as shown in tab - "Output (2)" - which is the same as level 2 of sub-total view in excel.

Please can someone guide? I already took a look at https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-add-a-subtotal-row-to-sum-values-belonging-to-specific/td-p/328064 this post and the suggestion isn't dynamic. I need the sub-total creation for all unique firm names instead of me passing the name of the firm in formula tool , since I am likely to have over 30 unique firm names.

Test.xlsx

Transformation

Predictive Analysis

Accepted answers

iCFO

It will be dynamic if you "group by" the Firm Name field for calculating subtotals. The tricky part is to place and space the subtotals correctly. For that you can use a record ids by group, the sum tool to get the "max", and then a formula to add a decimal behind it (like .1) and then sort it to drop it at the bottom row of the data. I also add a "Totals" column during the process. This allows me to reference that field and value for conditional formatting when I am using the reporting tools.

I added a "grand total" at the bottom as well.

Subtotal.yxzp

All comments

Christina_H

What do you want the output from Alteryx to look like? Just the subtotals? That's an easy job with a summarize tool.

iCFO

I added a "grand total" at the bottom as well.

Subtotal.yxzp

pdave87

@Mj9715 I believe this will match your expectations of dynamic subtotals inserted in new line with required statement and values. Let me know your thoughts. Same workflow is attached for your test run.

PD_Dynamic_Subtotal_29_09_2021.yxmd

Mj9715

Thanks all! @Christina_H , I was looking to add the sub-total line to the data rather than just summarize!

@iCFO this worked very well, especially the spaces placement worked perfectly, thanks 🙂

@pdave87 This works as well, however the sub total line needed to be placed in column one and also needed a grand total line. Barring that, this solution works!

iCFO

@Mj9715 Glad I could help! I also like to insert blank rows to clean up the presentation a bit before I start formatting with the report tools. Let me know if you need help there as well.

pdave87

@Mj9715 thanks for sharing this query/exercise. Even though the solution is provided by other SMEs, I stand to be corrected on my previous workflow shared yesterday.

Below workflow covers dynamic subtotals + grand total. Same is attached for your test run.

PD_Dynamic_Subtotal_GrandTotal_30Sep21.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703