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.

Sub and Grand Total

JDong
8 - Asteroid

Hi All,

 

Please refer to the attached flow where the subtotals and grandtotals are calculated for every 'Dept'.

 

Now I need to enhance the flow to accomodate filtering based on the column 'Sub-Dept'.

 

Eventually all columns in the dataset should be filterable and grandtotal should always be shown based on the selection. The subtotals work as expected.

 

Do let me know on any questions.

 

Gallop_0-1612187110211.png

 

Actually grand totals are in the Null 'Sub-Dept'

 

Thanks

12 REPLIES 12
joshuaburkhow
ACE Emeritus
ACE Emeritus

Hey @JDong 

 

There is already a tool that does sub totals and grand totals. It's part of the CrEW Macros

 

joshuaburkhow_0-1612188027604.png

 

Joshua Burkhow - Alteryx Ace | Global Alteryx Architect @PwC | Blogger @ AlterTricks
JDong
8 - Asteroid

Hi @joshuaburkhow

 

This is excellent...but in actual data I am working on the fields are strings 🙂

 

Anyway I get over this ? The reason they are strings are since the amount fields I appended with $.

 

Thanks

echuong1
Alteryx Alumni (Retired)

Unfortunately, it is not possible to have the numbers as a string and perform numeric functions to them. The values would need the "$" removed and the value converted to a number prior to any numeric function.

 

I suggest removing the "$" from your values, performing the calculations, and then adding them back at the end if you really need them. You can bulk remove and bulk add by using the multi-field formula and a replace function or a concatenate: 

replace([_CurrentField_],"$","") 

"$" + tostring([_CurrentField_])

 

Hope this helps!

JDong
8 - Asteroid

@echuong1 @joshuaburkhow 

 

Thanks if you can please read the question, I am already getting the grand total in the flow, problem is if I need to filter on the "Sub-Dept" it wont work.

 

Is there a way I can create a grandtotal where based on the lines for the Sub-Dept combinations ? So I can filter the data.

 

So according to me there will be 2 grand total combination lines ?

 

Thanks

echuong1
Alteryx Alumni (Retired)

Can you please include a sample dataset of what you have, and what you're looking for? If you could include your workflow, that would be helpful as well.

JDong
8 - Asteroid

@echuong1 , @joshuaburkhow  @jferrone 

 

Sure.

 

DeptSub DeptSalesCostMfgProfitTotal
MKTAfr2001001575390
MKTBar100101080200
MKT Total 30011025155590
ProdAfr1022620
ProdBar50224498
Prod Total 604450118
Grand Total 36011429205708


This is exacty the expected outcome. As in the attached flow. But now if the user filters on "Sub-Dept" he still wants to see the Grand Total row displayed and calculated for the Sub-Dept level.

Anyway to achieve this easily based on the outcome ? Thanks

jferrone
8 - Asteroid

The only way I could think of doing this is Summarize and Union tools. There might be a simpler/quicker method. Example workflow is attached. 

 

 

JDong
8 - Asteroid

Updated the output above

JDong
8 - Asteroid

Any update here please

@echuong1  @jferrone @joshuaburkhow 

Gallop_0-1612437040214.png

 

As we notice I add a filter on Sub-Dept the Grand total line is not seen. Any way to show the total for this ?

 

So output should be 

 

DeptSub DeptSalesCostMfgProfit
MKTMKT_CAN2001001575
MKTMKT _ US100101080
Grand Total 30011025155
Labels