topic Re: Sub and Grand Total in Alteryx Designer Desktop Discussions

Sub and Grand Total

JDong — Mon, 01 Feb 2021 13:56:26 GMT

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.

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

Thanks

Re: Sub and Grand Total

joshuaburkhow — Mon, 01 Feb 2021 14:00:34 GMT

Hey @JDong

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

Re: Sub and Grand Total

JDong — Mon, 01 Feb 2021 14:15:27 GMT

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

Re: Sub and Grand Total

echuong1 — Mon, 01 Feb 2021 16:36:01 GMT

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!

Re: Sub and Grand Total

JDong — Tue, 02 Feb 2021 14:53:33 GMT

@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

Re: Sub and Grand Total

echuong1 — Tue, 02 Feb 2021 14:56:28 GMT

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.

Re: Sub and Grand Total

JDong — Wed, 03 Feb 2021 17:22:34 GMT

@echuong1 , @joshuaburkhow @jferrone

Sure.

Dept	Sub Dept	Sales	Cost	Mfg	Profit	Total
MKT	Afr	200	100	15	75	390
MKT	Bar	100	10	10	80	200
MKT Total		300	110	25	155	590
Prod	Afr	10	2	2	6	20
Prod	Bar	50	2	2	44	98
Prod Total		60	4	4	50	118
Grand Total		360	114	29	205	708

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

Re: Sub and Grand Total

jferrone — Tue, 02 Feb 2021 15:30:49 GMT

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.

Re: Sub and Grand Total

JDong — Wed, 03 Feb 2021 11:13:57 GMT

Updated the output above

Re: Sub and Grand Total

JDong — Thu, 04 Feb 2021 11:17:29 GMT

Any update here please

@echuong1 @jferrone @joshuaburkhow

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

Dept	Sub Dept	Sales	Cost	Mfg	Profit
MKT	MKT_CAN	200	100	15	75
MKT	MKT _ US	100	10	10	80
Grand Total		300	110	25	155

Re: Sub and Grand Total

echuong1 — Thu, 04 Feb 2021 13:52:34 GMT

The issue is because you don't have any sort of indicator stating that that particular total line is associated with those records. You'd need another column that specified the value MKT, or add the value to the column you're filtering on, and filter on that.

Looking at your workflow and the way you're calculating the totals, it's not very dynamic. It works if you need the total by department, but what if you wanted value by region (US vs CAN)? I suggest doing the filter first, and then calculating the totals.

By Region:

Re: Sub and Grand Total

JDong — Thu, 04 Feb 2021 14:34:16 GMT

@echuong1

Thanks ! This helps but in my use case I will be applying the filter on a vizualization tool.

How can I handle this filtering on the reporting layer ? Any thoughts ?

Re: Sub and Grand Total

JDong — Mon, 08 Feb 2021 08:14:25 GMT

@echuong1

Seems a dead end ? Any tips please ?

Thanks