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,
Solved! Go to Solution.
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:
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:
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:
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.
Hi @Karlygash_M
Here's a third way that uses the built in Row and Column total functionality in the Crosstab tool
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
Always forget about the row/column total aggregation options @danilang!
thanks very much for your quick response
hey thanks a lot, learned a lot from your workflow 😊
thanks for offering an optimized solution
have a good day😉