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

Karlygash_M

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,

com.yxmd

screen.png

Workflow

Datasets

Developer Tools

Tips and Tricks

Custom Tools

Common Use Cases

Apps

Data Investigation

Accepted answers

Emmanuel_G

@Karlygash_M

Find in attachement the way of doing that.

Some long😅 but does the work automatically.

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

All comments

DataNath

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.

Add column and row totals.yxmd

Emmanuel_G

@Karlygash_M

Find in attachement the way of doing that.

Some long😅 but does the work automatically.

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

danilang

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

CrosstabTotals.yxmd

DataNath

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

Karlygash_M

thanks very much for your quick response

Karlygash_M

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

Karlygash_M

thanks for offering an optimized solution

have a good day😉

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703