Creating a dynamic "total" column for creating a frequency histogram.

[Neil Zhang]

I have some local data that is structured as follows:

A B C D E

1 0 0 1 0

1 1 0 0 0

2 0 1 1 2

0 0 0 1 0

1 0 3 1 0

Where each column contains only integers that represent a number of occurrences of that variable for a given row/observation. I want to create a column that sums every column in everyrow in a way that allows me to easily add/remove new columns without having to modify the custom expression in the "total" column.

I want to do this so that we can see how adding/removing a column affects the row totals so that I can count the number of rows with each total (e.g. 100 rows with 0 total occurences, 40 rows with 1 total occurence, 50 rows with 2 total occurences, etc) and create a histogram to visualize the distribution of total occurences.Currently I can only figure out how to make static "total" columns where I manually specify each column to sum but I'd rather just have a column that sums columns that exist in the table dynamically. It would also be nice to have an easy way to add/remove columns that exist in the original data table.

For example: All columns:

A B C D E Total

1 0 0 1 0 2

1 1 0 0 0 2

2 0 1 1 2 6

0 0 0 1 0 1

1 0 3 1 0 5

In this instance, I want to be able to create a histogram where there are 0 rows with 0 occurrences, 1 row with 1 occurrence, 2 rows with 2 occurrences, 0 rows with 3 & 4 occurrences, and 1 row each of 5 and 6 occurrences.

Only columns A, C, D included:

A C D Total

1 0 1 2

1 0 0 1

2 1 1 4

0 0 1 1

1 3 1 5

In this instance, I want to be able to create a histogram where there are 0 rows with 0 occurrences, 2 rows with 1 occurrence, 1 rows with 2 occurrences, 1 row with 4 occurrences, and 1 row with 5 occurrences.

My real data has many more columns and rows so I want to be able to set up a table/cross table so that I can see the effects of adding and removing rows on the resulting histogram.

Please let me know if you have any questions regarding the data or the desired output and I'll give as much detail as I can.

[Fabian Duerr]

You should change your data format.

First use 'insert' -> 'transformation' -> 'calculate new column':

Concatenate('Observaation_',String(RowId()))