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

Calculating total as a column and as a row for automatically updated table

Karlygash_M
8 - Asteroid

Good day colleagues, 

I have an issue with calculating total for columns and total for rows 

Lets say I have such table

name jan feb march october

a         1   2       5     1

b         2   2       5     1

c         3   2       5     1

 

And My output should be in this way, which total should be added by rows and by columns as well. I did with summarize tool, but the problem is that other months will be automatically added later. What can you advice to solve it?

 

name jan feb march october   nov (will be added automatically)     dec(will be added automatically)                    TOTAL

a         1   2       5     1              null for now                                                    null for now                                               8

b         1   2       5     1              null for now                                                    null for now                                               10

c         3   2       5     1              null for now                                                    null for now                                               11

Total   6   6       15    3             null for now                                                    null for now                                                 31

 

Thanks in advance, 

 

7 REPLIES 7
DataNath
17 - Castor

There's 2 routes to go down here @Karlygash_M - the first is incredibly simple and involves downloading the CReW macros pack - http://www.chaosreignswithin.com/p/macros.html - (Created by @MarqueeCrew and co.), which contains a macro called Add Totals that'll get you what you need:

 

DataNath_0-1666357690657.png

 

However, when a new field is added (as you mention, your table will be updated automatically), new fields won't be selected by default and so there would still be some manual input required to include these new months:

 

DataNath_1-1666357817107.png

 

For a dynamic approach, you can essentially rebuild what is under the hood of this, just with different configurations to allow for new and changing fields. The workflow relies on Transposing your data to bring the various headers into the data itself so that we can perform the various groupings and aggregations needed to get both row and column totals, as well as an overall total. The formula tool just applies the 'Total' label to the name field. The union/joins are just how we bring these totals back into the main data set to go alongside the individual values. By default, this will be dynamic to new incoming fields:

 

DataNath_2-1666357979807.png

DataNath_3-1666357988062.png

 

Hope this explanation helps get you on your way - please do let us know if not and we can continue to assist and help guide you along!


Also a note for the future: you didn't include a packaged workflow and so the data you used in your workflow can't be accessed by others, hence why I've used the mock up data from your post, rather than what might be present in your actual data file.

Emmanuel_G
13 - Pulsar

@Karlygash_M 

 

Find in attachement the way of doing that.

 

Some long😅 but does the work automatically.

 

Emmanuel_G_0-1666359246275.png

 

danilang
19 - Altair
19 - Altair

Hi @Karlygash_M 

 

Here's a third way that uses the built in Row and Column total functionality in the Crosstab tool 

danilang_0-1666440661557.png

danilang_0-1666441206414.png

 

Ideally, it would be a simple transpose/crosstab pair of tools after your input, except that the Crosstab tool 1) changes the column headers prepending Sum_, First_, etc and 2) can sometimes reorder the fields alphabetically.  To get around these limitations, you rename the fields to ascending integers, transpose/crosstab and then rename them back to the original. 

 

In the Build column rename info container, there is a Field Info tool, which generates the list of field names as a table, followed by a RecordID to number the fields.  The Formula tool builds the "Sum_" field name list. 

 

Dan   

 

DataNath
17 - Castor

Always forget about the row/column total aggregation options @danilang!

Karlygash_M
8 - Asteroid

thanks very much for your quick response

Karlygash_M
8 - Asteroid

hey thanks a lot, learned a lot from your workflow 😊

Karlygash_M
8 - Asteroid

thanks for offering an optimized solution

have a good day😉

Labels