Row calculations: running total, rank, share, group total
“A running total by month within each hospital”, “what place is each row in”, “what percent of the total is this” — in SQL these are window functions, in Excel formulas dragged down a column. In Tabulens such a calculation is built with a form and added as a calculated column.
Free to use. “Transform” → “Row calculation…”, or a column header menu → “Calculate by column…”. Windows version — 5.4 MB, no installation: details. Pro — $29 once: price.
What you can calculate
| Calculation | What the column holds |
|---|---|
| Row number in group | 1, 2, 3… restarting in each group |
| Running total | the sum of values from the start of the group to this row |
| Moving average | the average over a window of the number of rows you set |
| Difference from the previous row | the value minus the value one row above |
| Value from the previous or next row | the neighbour in order |
| Rank | the row’s place in the order; equal values share a place |
| Share of total, % | which part of the group’s sum the row makes up |
| Group total | sum, average, count, minimum or maximum of the group, shown on every row of it |
How to add one
- “Transform” → “Row calculation…”, or “Calculate by column…” in a column header menu, when the value column is already chosen.
- Pick what to calculate and the value column. To calculate within groups (per hospital, per customer), tick the columns in “Within groups”; with none ticked the whole table is one group.
- In “Row order” say how rows follow each other — by date, for example. A running total and a difference depend on the order, so it is set explicitly rather than taken from the screen; rank requires an order.
- Name the column and press “Add”.
Good to know
- The order and groups are part of the calculation. However the tab is sorted, the running total comes out the same. With no order set, file order is used.
- The filter does not affect it. All rows except those marked for deletion are counted: a total “as of March 1” does not change with what happens to be filtered.
- The column is computed. The file does not change, and the values recalculate after edits. Export the table, or write the values into the column, to get them into a file.
- The operation goes into the tab’s log, so it can be saved as a scenario step and repeated on the next file.
FAQ
How is this different from a pivot table?
A pivot collapses rows into totals. A row calculation keeps every row in place and adds a column, so each row shows both its own value and the running total or share.
How do I rank within a group?
Pick “Rank”, tick the group column in “Within groups” and set the order: by the amount column, descending, the largest gets first place. Rows with equal order values share a place.
What happens to empty values?
Empty and non-numeric values are skipped when summing and averaging. In “Difference from the previous row” the first row of a group stays empty, and so does a row where either of the two values is not a number.
Free to use. Your file is not uploaded to a server. Windows version — 5.4 MB, no installation: details. Pro — $29 once: price.